列：使用jdbc连接数据库实现查询数据库

导入包

sql语句、创建订单表和用户表

/*
Navicat MySQL Data TransferSource Server         : localhost_3306
Source Server Version : 50521
Source Host           : localhost:3306
Source Database       : mybatisTarget Server Type    : MYSQL
Target Server Version : 50521
File Encoding         : 65001Date: 2015-04-09 16:03:53
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (`id` INT(11) NOT NULL AUTO_INCREMENT,`user_id` INT(11) NOT NULL COMMENT '下单用户id',`number` VARCHAR(32) NOT NULL COMMENT '订单号',`createtime` DATETIME NOT NULL COMMENT '创建订单时间',`note` VARCHAR(100) DEFAULT NULL COMMENT '备注',PRIMARY KEY (`id`),KEY `FK_orders_1` (`user_id`),CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', NULL);
INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', NULL);
INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', NULL);-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (`id` INT(11) NOT NULL AUTO_INCREMENT,`username` VARCHAR(32) NOT NULL COMMENT '用户名称',`birthday` DATE DEFAULT NULL COMMENT '生日',`sex` CHAR(1) DEFAULT NULL COMMENT '性别',`address` VARCHAR(256) DEFAULT NULL COMMENT '地址',PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', NULL, '2', NULL);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', NULL, NULL, NULL);

代码实现

写代码时导入jdbc的相关包

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class JdbcDemo {public static void main(String[] args) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {// 加载数据库驱动Class.forName("com.mysql.jdbc.Driver");// 通过驱动管理类获取数据库链接connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "123");// 定义sql语句 ?表示占位符String sql = "select * from user where username = ?";// 获取预处理statementpreparedStatement = connection.prepareStatement(sql);// 设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值preparedStatement.setString(1, "王五");// 向数据库发出sql执行查询，查询出结果集resultSet = preparedStatement.executeQuery();// 遍历查询结果集while (resultSet.next()) {System.out.println(resultSet.getString("id") + "  " + resultSet.getString("username"));}} catch (Exception e) {e.printStackTrace();} finally {// 释放资源if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (preparedStatement != null) {try {preparedStatement.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}
}

结果

jdbc问题总结如下

1. 数据库连接创建、释放频繁造成系统资源浪费，从而影响系统性能。如果使用数据库连接池可解决此问题。
2. Sql语句在代码中硬编码，造成代码不易维护，实际应用中sql变化的可能较大，sql变动需要改变java代码。
3. 使用preparedStatement向占有位符号传参数存在硬编码，因为sql语句的where条件不一定，可能多也可能少，修改sql还要修改代码，系统不易维护。
4. 对结果集解析存在硬编码（查询列名），sql变化导致解析代码变化，系统不易维护，如果能将数据库记录封装成pojo对象解析比较方便。

mybatis下载

mybaits的代码由github.com管理，下载地址：https://github.com/mybatis/mybatis-3/releases

下载后文件展示

mybatis api地址

列：mybatis项目练习：使用mapper动态代理方式实现各种查询方式

模糊查询、数据条数查询、if与where查询、array、list、包装类查询、一对一查询、一对多查询

导包

1导入log4j文件 log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

数据库连接参数JDBC.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
jdbc.name=root
jdbc.password=123

SqlMapConfig.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><!--加载资源文件 --><properties resource="JDBC.properties"></properties><!-- 别名 --><typeAliases><package name="com.lws.mybatis.po" /></typeAliases><environments default="develompent"><environment id="develompent"><!-- 使用jdbc事务--><transactionManager type="JDBC"></transactionManager><!-- 数据库连接池 --><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.name}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!-- Mapper的位置  Mapper.xml 写Sql语句的文件的位置 --><mappers><!--<package name="com.lws.user.userdao"/> --><package name="com.lws"/></mappers>
</configuration>

创建po类

实现mapper接口

UserMapper.java

public interface UserMapper {
public User findUserById(Integer id);
public List<User> findUserByQueryVo(QueryVo vo);//2根据用户名模糊查询用户信息，查询条件放到QueryVo的user属性中。
public Integer countUser();//3查询数据条数
public List<User> selectUserBySexAndUserName(User user);//4 根据姓名或性别查询用户（动态sql语句 ）
public List<User> selectUserByidsArray(Integer[] id);//5 通过id数组查询用户
public List<User> selectUserByidsList(List<Integer> id);//6 通过id列表查询用户
public List<User> selectUserByidsPo(QueryVo vo);//7 通过id包装类查询用户
//-------------------------------------------
public List<Orders> selectOrder();//9查询订单表所有数据
//10  1对1查询 根据订单查询用户
public List<Orders> selectOrders();
//11 一对多查询 根据用户查询订单
public List<User> selectUserList();
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lws.mybatis.mapper.UserMapper"><!-- 1 通过id查询一个用户 --><select id="findUserById" resultType="User">select* from user where id=#{v}</select><!-- 2模糊查询user的生成对象 --><select id="findUserByQueryVo" parameterType="QueryVo" resultType="User">select * from user where username like "%"#{user.username}"%"</select><!-- 3查询数据条数 --><select id="countUser" resultType="Integer">select count(1) from user</select><!--4 根据姓名和性别查询用户（动态sql语句 ）  --><select id="selectUserBySexAndUserName" resultType="User">select * from user<where><if test="sex!=null and sex!=''">and sex=#{sex}</if><if test="username!=null and username!=''">and username=#{username}</if></where></select><!-- 5 通过id数组查询用户 --><select id="selectUserByidsArray" resultType="User">select * from user<where><foreach collection="array" item="id" open="id in(" close=")"separator=",">#{id}</foreach></where></select><!-- 6 通过id列表查询用户 --><select id="selectUserByidsList" resultType="User">select * from user<where><foreach collection="list" item="id" open="id in (" close=")"separator=",">#{id}</foreach></where></select><!--7 通过id包装类查询用户  --><select id="selectUserByidsPo" parameterType="QueryVo"  resultType="User"><include refid="mysql"></include><where><foreach collection="ids" item="id" open="id in (" close=")"separator=",">#{id}</foreach></where></select><!--8 动态sql语句  --><sql id="mysql">select * from user</sql><!-- 9 查询订单表所有数据  表属性与po属性不同-->
<!-- 	<resultMap type="Orders" id="aa"><result column="user_id" property="userId" /></resultMap><select id="selectOrder" resultType="aa">SELECT id, user_id, number, createtime, note FROM orders</select>  --><!-- 10 一对一关联 查询  以订单为中心 关联用户 --><resultMap type="Orders" id="cc"><result column="id" property="id"/><result column="user_id" property="userId"/><result column="number" property="number"/><association property="user" javaType="User"><id column="user_id" property="id"/><result column="username" property="username"/></association></resultMap><select id="selectOrders" resultMap="cc">SELECT 	o.id,o.user_id, o.number,o.createtime,u.username FROM orders o left join user u on o.user_id = u.id</select><!--11 一对多查询 根据用户查询订单 可以使用左连接、left join: 左连接 on连接条件  --><resultMap type="User" id="bbb"><id column="user_id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><result column="birthday" property="birthday"/><result column="address" property="address"/><collection property="orders" ofType="Orders"><id column="oid" property="id"/><result column="number" property="number"/><result column="createtime" property="createtime"/><result column="note" property="note"/></collection> </resultMap><select id="selectUserList" resultMap="bbb">SELECT o.id as oid,  o.user_id,  o.number,o.createtime,u.usernameFROM user uinner join orders oon o.user_id = u.id</select></mapper>

测试

public class MybatisMapperTest {public SqlSessionFactory sqlSessionFactory;@Beforepublic void Before() throws IOException {// 加载核心配置文件String resource = "sqlMapConfig.xml";InputStream in = Resources.getResourceAsStream(resource);// 创建sqlSessionFactorysqlSessionFactory = new SqlSessionFactoryBuilder().build(in);}@Testpublic void findUserById() {SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）User u = mapper.findUserById(10);System.err.println(u);}@Testpublic void findUserByQueryVo() {SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）QueryVo vo=new QueryVo();User user=new User();user.setUsername("五");vo.setUser(user);List<User> u = mapper.findUserByQueryVo(vo);for (User user2 : u) {System.err.println(user2);}}@Testpublic void countUser() {SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）Integer countUser = mapper.countUser();System.err.println(countUser);}/*** 4 根据姓名或性别查询用户（动态sql语句 ）*/@Testpublic void selectUserBySexAndUserName() {SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）User user=new User();user.setSex("1");
//        user.setUsername("张小明");List<User> users = mapper.selectUserBySexAndUserName(user);for (User user2 : users) {System.err.println(user2);}}/*** 5 通过id数组查询用户*/@Testpublic void selectUserByidsArray() {SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）Integer[] ids=new Integer[3];ids[0]=24;ids[1]=25;ids[2]=26;List<User> users = mapper.selectUserByidsArray(ids);for (User user2 : users) {System.err.println(user2);}}/*** 6 通过id列表查询用户*/@Testpublic void selectUserByidsList() {SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）List<Integer> list=new ArrayList<>();list.add(1);list.add(10);list.add(16);List<User> users = mapper.selectUserByidsList(list);for (User user2 : users) {System.err.println(user2);}}/***7 通过id包装类查询用户*/@Testpublic void selectUserByidsPo() {System.err.println("7 通过id包装类查询用户");SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）QueryVo vo=new QueryVo();List<Integer> ids  = new ArrayList<>();ids.add(16);ids.add(22);ids.add(24);vo.setIds(ids);List<User> users=mapper.selectUserByidsPo(vo);for (User user2 : users) {System.err.println(user2);}}/***9查询订单表所有数据*/@Testpublic void selectOrder() {System.err.println("7 通过id包装类查询用户");SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion帮我生成一个实现类  （给接口）List<Orders> orders = mapper.selectOrder();for (Orders o : orders) {System.err.println(o);}}/***10一对一查询：查询订单表所有数据*/@Testpublic void selectOrders() {System.err.println("7 通过id包装类查询用户");SqlSession sqlSession = sqlSessionFactory.openSession(); //创建SqlSessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//SqlSEssion生成一个实现类  （给接口）List<Orders> orders = mapper.selectOrders();for (Orders o : orders) {System.err.println(o);}}/*** 11 一对多查询*/@Testpublic void selectUserList() {System.err.println("一对多查询");SqlSession sqlSession = sqlSessionFactory.openSession();//创建sqlsessionUserMapper mapper = sqlSession.getMapper(UserMapper.class);//使用sqlSession生成mapperList<User> users= mapper.selectUserList();for (User user : users) {System.err.println(user);}}
}