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:1uid:13uname:lucyrole: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:1uid:13uname:lucyrole: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}]