jdbc与mybatis及spring

java中做数据库查询最直接的是用jdbc来做，有了mybatis之后，对象的映射关系就可以通过mybatis的xml文件来配置，

spring又提供了良好的框架，集成mybatis，使得使用更方便，但同时spring也可以直接支持jdbc的对象数据映射

如下就分别以jdbc，mybatis，spring-jdbc, spring-mybatis来做一个查询

如数据库中有一个表

我们引入junit来做代码测试，导入junit-4.7.jar，spring-test-3.2.8.RELEASE.jar

1、如果只用jdbc的话，我们只需要引入mysql驱动包，如mysql-connector-java-5.1.31.jar

然后代码如下：

  private Connection conn = null;

  @Before
  public void before() {
    try {
      conn = DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/press?useUnicode=true&characterEncoding=UTF-8",
          "test", "test");
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  @Test
  public void testJdbcQuery() throws SQLException {
    String sql = "select * from user where id=1";
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    ResultSetMetaData meta = rs.getMetaData();
    int count = meta.getColumnCount();
    int cnt = 0;
    while (rs.next()) {
      cnt ++;
      for (int i = 1; i <= count; i++) {
        System.out.println(meta.getColumnName(i) + ":" + rs.getString(i));
      }
    }
    Assert.assertTrue(cnt >= 0);;
  }

  @After
  public void after() {
    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

结果为：

id:1
uid:13
uname:lucy
role:0

2、如果用mybatis，则需要加入依赖mybatis-3.2.7.jar

在classpath下面配置文件：

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/press?useUnicode=true&characterEncoding=UTF-8" />
                <property name="username" value="test" />
                <property name="password" value="test" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mybatis/user_mapper.xml" />
    </mappers>
</configuration>

在classpath/mybatis下面配置文件user_mapper.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="User">
	<select id="selectUser" parameterType="java.lang.String"
		resultType="java.util.Map"><![CDATA[
        select * from user where id = #{id}
    ]]></select>

	<select id="selectUsers" resultType="java.util.Map"><![CDATA[
        select * from user;
    ]]></select>
</mapper>

然后代码如下：

  private SqlSession session;

  @Before
  public void before() {
    String resource = "mybatis.xml";
    try {
      Reader reader = Resources.getResourceAsReader(resource);
      SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
      session = ssf.openSession();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }

  @Test
  public void getMap() {
    Map<String, Object> map = session.selectOne("selectUser", "1");
    System.out.println(map);
    Assert.assertNotNull(map);
  }

  @Test
  public void getMaps() {
    List<Object> maps = session.selectList("selectUsers");
    System.out.println(maps);
    Assert.assertNotNull(maps);
  }

  @After
  public void after() {
    if (session != null) {
      session.close();
    }
  }

结果为：

{uid=13, role=0, uname=lucy, id=1}
[{uid=13, role=0, uname=lucy, id=1}, {uid=12, role=1, uname=sam, id=2}]

接下来要引入spring框架，spring关于数据库的操作都需要Datasource的支持，spring框架也需要core, context, tx的支持

因此先引入

aopalliance-1.0.jar,commons-logging-1.1.1.jar,mysql-connector-java-5.1.31.jar,spring-aop-3.1.1.RELEASE.jar,spring-asm-3.1.1.RELEASE.jar,spring-beans-3.1.1.RELEASE.jar,spring-context-3.1.1.RELEASE.jar,spring-core-3.1.1.RELEASE.jar,spring-expression-3.1.1.RELEASE.jar,spring-jdbc-3.1.1.RELEASE.jar,spring-tx-3.1.1.RELEASE.jar
3、再看看spring-jdbc写法：

先配置dataSource.xml，这里用的是spring-jdbc的DriverManagerDataSource，当然也可以用dbcpCommonDatasource

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"
	default-autowire="byType">
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost:3306/press?useUnicode=true&characterEncoding=UTF-8"/>
		<property name="username" value="test"/>
		<property name="password" value="test"/>
	</bean>

</beans>

然后配置spring-jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"
	default-autowire="byType">
	<import resource="classpath:datasource.xml" />
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg index="0" ref="dataSource"/>
	</bean>
</beans>

然后就可以写测试代码了：

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-jdbc.xml"})
public class SpringJdbcTest {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Test
  public void testJdbcSelect() {
    jdbcTemplate.query("select * from job_user where id=?", new Object[] {1},
        new RowMapper<String>() {
          public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            ResultSetMetaData meta = rs.getMetaData();
            int count = meta.getColumnCount();
            for (int i = 1; i <= count; i++) {
              System.out.println(meta.getColumnName(i) + ":" + rs.getString(i));
            }
            return null;
          }
        });
  }
}

输出结果为：

id:1
uid:13
uname:lucy
role:0

如果是用spring整合mybatis的话，我们只需要再加入mybatis-spring-1.2.2.jar

然后配置spring-mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"
	default-autowire="byType">
	<import resource="classpath:datasource.xml" />
	<bean id="sessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0">
			<bean class="org.mybatis.spring.SqlSessionFactoryBean">
				<property name="dataSource" ref="dataSource" />
				<property name="mapperLocations" value="classpath:mybatis/*_mapper.xml"></property>
			</bean>
		</constructor-arg>
	</bean>
</beans>

然后再写测试代码：

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring-mybatis.xml"})
public class SpringMybatisTest {

  @Autowired
  private SqlSessionTemplate sqlSession;

  @Test
  public void testSelect() {
    Object ret = sqlSession.selectOne("User.selectUser", "1");
    System.out.println(ret);
  }

  @Test
  public void testBatch() {
    Object ret = sqlSession.selectList("User.selectUsers");
    System.out.println(ret);
  }
}

结果为：

{uid=13, role=0, uname=lucy, id=1}
[{uid=13, role=0, uname=lucy, id=1}, {uid=12, role=1, uname=sam, id=2}]