MySQL JDBC与JDBCTemplate和Mybatis比较

几种JDBC比较

我们来比较一下JDBC，JDBCTemplate，MyBatis对MySQL数据库的操作，如下

JDBC

查找所有

package com.zj.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.zj.dao.EmpDao;
import com.zj.entity.Emp;
import com.zj.util.DBUtil;

public class EmpDaoImpl implements EmpDao{
	
	private Connection cn=null;
	private PreparedStatement ps=null;
	private ResultSet rs=null;

	//查找所有
	@Override
	public List<Emp> findAll() {
		List<Emp> list = new ArrayList<Emp>();
		
		try {
			cn=DBUtil.getConnection();
			String sql = "select * from emp";
			ps=cn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Emp emp = new Emp(rs.getInt(1),rs.getString(2),rs.getDouble(3),
						rs.getDouble(4),rs.getDate(5),rs.getInt(6));
				list.add(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.close(cn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

}

根据id删除

	
	@Override
	public void deleteById(int id) {
		try {
			cn=DBUtil.getConnection();
			String sql = "delete from emp where empno=?";
			ps=cn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				DBUtil.close(cn, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

添加一条记录

	
	@Override
	public void addOne(Emp emp) {
		try {
			cn=DBUtil.getConnection();
			String sql = "insert into emp values(?,?,?,?,?,?)";
			ps=cn.prepareStatement(sql);
			ps.setInt(1, emp.getEmpno());
			ps.setString(2, emp.getEname());
			ps.setDouble(3, emp.getSalary());
			ps.setDouble(4, emp.getBonus());
			ps.setDate(5, emp.getHiredate());
			ps.setInt(6, emp.getDeptno());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				DBUtil.close(cn, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

更新一条记录

	@Override
	public void updateOne(Emp emp) {
		System.out.println("更新操作");
		System.out.println(emp);
		try {
			cn=DBUtil.getConnection();
			String sql = "update emp set ename=?,salary=?,bonus=?,hiredate=?,deptno=? where empno=?";
			ps=cn.prepareStatement(sql);
			ps.setString(1, emp.getEname());
			ps.setDouble(2, emp.getSalary());
			ps.setDouble(3, emp.getBonus());
			ps.setDate(4, emp.getHiredate());
			ps.setInt(5, emp.getDeptno());
			ps.setInt(6, emp.getEmpno());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				DBUtil.close(cn, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

根据Id查找记录

	@Override
	public Emp findOne(int id) {
		Emp emp = null;
		try {
			cn=DBUtil.getConnection();
			String sql = "select * from emp where empno=?";
			ps=cn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while (rs.next()) {
				emp = new Emp(rs.getInt(1),rs.getString(2),rs.getDouble(3),
						rs.getDouble(4),rs.getDate(5),rs.getInt(6));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				DBUtil.close(cn, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return emp;
	}

JdbcTemplate

XML

<!-- 配置数据库 -->
<util:properties id="db" location="classpath:db.properties"/>

<!-- 声明BasicDataSource -->
<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource">
	<property name="driverClassName" value="#{db.driver}"/>
	<property name="url" value="#{db.url}"/>
	<property name="username" value="#{db.user}"/>
	<property name="password" value="#{db.pwd}"/>
</bean>

<!-- 声明jdbcTemplate -->
<bean class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="ds"/>
</bean>

代码

package com.zj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.zj.entity.Emp;


@Repository
public class EmpDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	//查找所有
	public List<Emp> findAll(){
		String sql="select * from emp";
		List<Emp> emps = jdbcTemplate.query(sql, new EmpRowMapper());
		return emps;
	}

	//根据id查找
	public Emp findByEmpno(int empno) {
		String sql="select * from emp where empno=?";
		Emp emp = jdbcTemplate.queryForObject(sql, new Object[] {empno},
				new EmpRowMapper());
		System.out.println(emp);
		return emp;
	}
	
	//新增
	public void addEmp(Emp emp) {
		String sql="insert into emp values(?,?,?,?,?,?)";
		Object[] param= {emp.getEmpno(),emp.getEname(),emp.getSalary(),
				emp.getBonus(),emp.getHiredate(),emp.getDeptno()};
		jdbcTemplate.update(sql, param);
	}
	
	//更新
	public void updateEmp(Emp emp) {
		System.out.println("update");
		String sql="update emp set ename=?,salary=?,"
				+ "bonus=?,hiredate=?,deptno=? where empno=?";
		Object[] param= {emp.getEname(),emp.getSalary(),emp.getBonus(),
				emp.getHiredate(),emp.getDeptno(),emp.getEmpno()};
		jdbcTemplate.update(sql, param);
	}
	
	//删除
	public void deleteEmp(int empno) {
		String sql="delete from emp where empno=?";
		jdbcTemplate.update(sql, new Object[] {empno});
	}
	
	//集合映射
	class EmpRowMapper implements RowMapper<Emp>{
		@Override
		public Emp mapRow(ResultSet rs, int index) throws SQLException {
			Emp emp=new Emp(
					rs.getInt(1),
					rs.getString(2),
					rs.getDouble(3),
					rs.getDouble(4),
					rs.getDate(5),
					rs.getInt(6));
			return emp;
		}
	}
	
}

Mybatis

XML

基于Spring的配置

<?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="dev">
		<environment id="dev">
			<transactionManager type="JDBC"/>
			<dataSource   type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/jsd"/>
				<property name="username" value="root"/>
				<property name="password" value="root"/>
			</dataSource>
		</environment>
	</environments>

	
	<!-- 引入mapper配置文件 -->
	<mappers>
		<mapper resource="com/zj/entity/mapper/DeptMapper.xml"/>
	</mappers>
</configuration>

XML

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="com.zj.dao.DeptDao">
	<!-- findAll -->
	<select id="findAll" resultType="com.zj.entity.Dept">
		select * from dept
	</select>
	
	<!-- findOne  -->
	<select id="findByDeptno" parameterType="java.lang.Integer" resultType="com.zj.entity.Dept">
		select * from dept where deptno = #{deptno}
	</select>
	
	<!-- add -->
	<insert id="add" parameterType="com.zj.entity.Dept">
		insert into dept values(#{deptno},#{dname})
	</insert>
	
	<!-- update -->
	<update id="update" parameterType="com.zj.entity.Dept">
		update dept set dname=#{dname} where deptno=#{deptno}
	</update>
	
	<!-- delete -->
	<delete id="delete" parameterType="java.lang.Integer">
		delete from dept where deptno=#{id}
	</delete>
	
</mapper>

接口

package com.zj.dao;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.zj.entity.Dept;
import com.zj.util.MyBatisUtil;

public class DeptDao {
	
	public List<Dept> findAll(){
		SqlSession ss=MyBatisUtil.getSession(true);
		List<Dept> depts = ss.selectList("com.zj.dao.DeptDao.findAll");
		ss.close();
		return depts;
	}
	
	public Dept findByDeptno(int deptno) {
		SqlSession ss=MyBatisUtil.getSession(true);
		Dept dept = ss.selectOne("com.zj.dao.DeptDao.findByDeptno", deptno);
		ss.close();
		return dept;
	}
	
	public void add(Dept dept) {
		SqlSession ss=MyBatisUtil.getSession(true);
		ss.insert("com.zj.dao.DeptDao.add", dept);
		ss.close();
	}
	
	public void update(Dept dept) {
		SqlSession ss=MyBatisUtil.getSession(true);
		ss.update("com.zj.dao.DeptDao.update", dept);
		ss.close();
	}
	
	public void delete(int deptno) {
		SqlSession ss=MyBatisUtil.getSession(true);
		ss.delete("com.zj.dao.DeptDao.delete",deptno);
		ss.close();
	}
}