[转载]javaEE学习篇之——JDBC详解原文链接：http://blog.csdn.net/jiangwei0910410003/article/details/26164629
目录1.摘要2.JDBC的使用步骤1.注册驱动 只做一次2.建立连接Connection 3.创建执行SQL的语句Statement4.处理执行结果ResultSet5.释放资源3.使用JDBC来实现CRUD的操作4.Statement中的sql依赖注入的问题5.JDBC中特殊数据类型的操作问题1.第一个是日期问题2.第二个问题就是大文本数据的问题6.JDBC中事务的概念7.JDBC中调用存储过程8.JDBC来实现批处理功能9.JDBC中的滚动结果集和分页技术10.JDBC中的可更新以及对更新敏感的结果集操作11.元数据的相关知识1.数据库的元数据信息2.查询参数的元数据信息3.结果集中元数据信息12.JDBC中的数据源13.JDBC中CRUD的模板模式14.SPRing框架中的JdbcTemplate1.加强版的JdbcTemplate1.NamedParameterJdbcTemplate2.SimpleJdbcTemplate
今天我们来说一下关于JDBC的相关知识，关于JDBC我想大家都不陌生了，而且我记得早就开始使用它了，记得那是大二的时候做课程设计，但是那时候是为了完成任务，所以遇到问题就google,那时候也没有时间去整理，所以这次就来详细说一下关于JDBC的知识
摘要：
JDBC（Java Data Base Connectivity,java数据库连接），由一些接口和类构成的API。 J2SE的一部分，由java.sql,javax.sql包组成。
应用程序、JDBC API、数据库驱动及数据库之间的关系
JDBC的使用步骤
1.注册驱动 (只做一次)
方式一：Class.forName(“com.MySQL.jdbc.Driver”); 推荐这种方式，不会对具体的驱动类产生依赖。 方式二：DriverManager.registerDriver(com.mysql.jdbc.Driver); 会造成DriverManager中产生两个一样的驱动，并会对具体的驱动类产生依赖。 方式三：System.setProperty(“jdbc.drivers”, “driver1:driver2”); 虽然不会对具体的驱动类产生依赖；但注册不太方便，所以很少使用。 驱动类型(四种类型)
2.建立连接(Connection)
[java] view plaincopyprint?Connectionconn=DriverManager.getConnection(url,user,passWord);
Connection conn = DriverManager.getConnection(url, user, password);
url格式： JDBC:子协议:子名称//主机名:端口/数据库名？属性名=属性值&… User,password可以用“属性名=属性值”方式告诉数据库； 其他参数如：useUnicode=true&characterEncoding=GBK。
3.创建执行SQL的语句(Statement)
[java] view plaincopyprint?StatementStatementst=conn.createStatement();st.executeQuery(sql);PreparedStatementStringsql=“select*fromtable_namewherecol_name=?”;PreparedStatementps=conn.preparedStatement(sql);ps.setString(1,“col_value”);ps.executeQuery();
StatementStatement st = conn.createStatement();st.executeQuery(sql);PreparedStatementString sql = “select * from table_name where col_name=?”;PreparedStatement ps = conn.preparedStatement(sql);ps.setString(1, “col_value”);ps.executeQuery();
4.处理执行结果(ResultSet)
[java] view plaincopyprint?ResultSetrs=statement.executeQuery(sql);While(rs.next()){rs.getString(“col_name”);rs.getInt(“col_name”);//…}
ResultSet rs = statement.executeQuery(sql);While(rs.next()){rs.getString(“col_name”);rs.getInt(“col_name”);//…}
5.释放资源释放ResultSet, Statement,Connection. 数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
下面来看一下完整的Demo:
工具类：JdbcUtils
[java] view plaincopyprint?packagecom.weijia.firstdemo;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjavax.sql.DataSource;publicclassJdbcUtils{privatestaticStringuser="root";privatestaticStringpassword="123456";privatestaticStringdbName="test";privatestaticStringurl="jdbc:mysql://localhost:3306/"+dbName+"?user="+user+"&password="+password+"&useUnicode=true&characterEncoding=gb2312";privatestaticDataSourcedataSource=null;/***加载驱动*/static{try{Class.forName("com.mysql.jdbc.Driver");}catch(Exceptione){System.out.println("Exception:"+e.getMessage()+"");thrownewExceptionInInitializerError(e);}}privateJdbcUtils(){}/***获取连接*@return*@throwsSQLException*/publicstaticConnectiongetConnection()throwsSQLException{returnDriverManager.getConnection(url);}publicstaticDataSourcegetDataSource(){returndataSource;}/***释放资源*@paramrs*@paramst*@paramconn*/publicstaticvoidfree(ResultSetrs,Statementst,Connectionconn){try{if(rs!=null){rs.close();}}catch(SQLExceptione){e.printStackTrace();}finally{try{if(st!=null){st.close();}}catch(SQLExceptione){e.printStackTrace();}finally{try{if(conn!=null){conn.close();}}catch(SQLExceptione){e.printStackTrace();}}}}}
package com.weijia.firstdemo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.sql.DataSource;public class JdbcUtils {private static String user = "root";private static String password = "123456";private static String dbName = "test";private static String url = "jdbc:mysql://localhost:3306/"+dbName+"?user="+user+"&password="+password+"&useUnicode=true&characterEncoding=gb2312";private static DataSource dataSource = null;/** * 加载驱动 */static{try{Class.forName("com.mysql.jdbc.Driver");}catch(Exception e){System.out.println("Exception:"+e.getMessage()+"");throw new ExceptionInInitializerError(e);}}private JdbcUtils(){}/** * 获取连接 * @return * @throws SQLException */public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url);}public static DataSource getDataSource(){return dataSource;}/** * 释放资源 * @param rs * @param st * @param conn */public static void free(ResultSet rs,Statement st,Connection conn){try{if(rs != null){rs.close();}}catch(SQLException e){e.printStackTrace();}finally{try{if(st != null){st.close();}}catch(SQLException e){e.printStackTrace();}finally{try{if(conn != null){conn.close();}}catch(SQLException e){e.printStackTrace();}}}}}
测试类：
[java] view plaincopyprint?packagecom.weijia.firstdemo;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassDemo{publicstaticvoidmain(String[]args)throwsException{//测试代码：test();//标准规范代码：template();}//模板代码publicstaticvoidtemplate(){Connectionconn=null;Statementst=null;ResultSetrs=null;try{conn=JdbcUtils.getConnection();//创建语句st=conn.createStatement();//执行语句rs=st.executeQuery("select*fromuser");//处理结果while(rs.next()){System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3)+"\t");}}catch(SQLExceptione){e.printStackTrace();}catch(Exceptione){e.printStackTrace();}finally{JdbcUtils.free(rs,st,conn);}}//测试staticvoidtest()throwsException{//注册驱动DriverManager.registerDriver(newcom.mysql.jdbc.Driver());//通过系统属性来注册驱动System.setProperty("jdbc.drivers","");//静态加载驱动Class.forName("com.mysql.jdbc.Driver");//建立连接Stringurl="jdbc:mysql://localhost:3306";StringuserName="root";Stringpassword="";Connectionconn=DriverManager.getConnection(url,userName,password);//创建语句Statementst=conn.createStatement();//执行语句ResultSetrs=st.executeQuery("select*fromuser");//处理结果while(rs.next()){System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3)+"\t");}//释放资源rs.close();st.close();conn.close();}}
package com.weijia.firstdemo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Demo {public static void main(String[] args) throws Exception{//测试代码：test();//标准规范代码：template();}//模板代码public static void template(){Connection conn = null;Statement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();//创建语句st = conn.createStatement();//执行语句rs = st.executeQuery("select * from user");//处理结果while(rs.next()){System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t" + rs.getObject(3) + "\t");}}catch(SQLException e){e.printStackTrace();}catch(Exception e){e.printStackTrace();}finally{JdbcUtils.free(rs, st, conn);}}//测试static void test() throws Exception{//注册驱动DriverManager.registerDriver(new com.mysql.jdbc.Driver());//通过系统属性来注册驱动System.setProperty("jdbc.drivers","");//静态加载驱动Class.forName("com.mysql.jdbc.Driver");//建立连接String url = "jdbc:mysql://localhost:3306";String userName = "root";String password = "";Connection conn = DriverManager.getConnection(url,userName,password);//创建语句Statement st = conn.createStatement();//执行语句ResultSet rs = st.executeQuery("select * from user");//处理结果while(rs.next()){System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t" + rs.getObject(3) + "\t");}//释放资源rs.close();st.close();conn.close();}}
注意：这里还要记住引入额外的jar.这个网上很多的，这里使用的是MySql,搜一下MySql驱动的jar就行了。这里我们将一些操作都放到一个工具类中，这种方式是很优雅的。
使用JDBC来实现CRUD的操作
我们这里就采用分层操作：Dao层，Service层
首先看一下domain域中的User实体
[java] view plaincopyprint?packagecom.weijia.domain;importjava.util.Date;publicclassUser{privateintid;privateStringname;privateDatebirthday;privatefloatmoney;publicUser(){}publicUser(intid,Stringname,Datebirthday,floatmoney){this.id=id;this.name=name;this.birthday=birthday;this.money=money;}publicintgetId(){returnid;}publicvoidsetId(intid){this.id=id;}publicStringgetName(){returnname;}publicvoidsetName(Stringname){this.name=name;}publicDategetBirthday(){returnbirthday;}publicvoidsetBirthday(Datebirthday){this.birthday=birthday;}publicfloatgetMoney(){returnmoney;}publicvoidsetMoney(floatmoney){this.money=money;}@OverridepublicStringtoString(){return"[id="+id+",name="+name+",birthday="+birthday+",money="+money+"]";}}
package com.weijia.domain;import java.util.Date;public class User {private int id;private String name;private Date birthday;private float money;public User(){}public User(int id,String name,Date birthday,float money){this.id = id;this.name = name;this.birthday = birthday;this.money = money;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public float getMoney() {return money;}public void setMoney(float money) {this.money = money;}@Overridepublic String toString(){return "[id="+id+",name="+name+",birthday="+birthday+",money="+money+"]";}}
再来看一下Dao层结构：
接口：
[java] view plaincopyprint?packagecom.weijia.domain;publicinterfaceUserDao{//添加用户publicvoidaddUser(Useruser);//通过userid查询用户,id是唯一的,所以返回的是一个userpublicUsergetUserById(intuserId);//更新用户信息publicintupdate(Useruser);//删除用户信息publicintdelete(Useruser);}
package com.weijia.domain;public interface UserDao {//添加用户public void addUser(User user);//通过userid查询用户,id是唯一的,所以返回的是一个userpublic User getUserById(int userId);//更新用户信息public int update(User user);//删除用户信息public int delete(User user);}
实现类：
[java] view plaincopyprint?packagecom.weijia.domain;importjava.sql.Connection;importjava.sql.Date;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importcom.weijia.firstdemo.JdbcUtils;publicclassUserDaoImplimplementsUserDao{/***添加用户*/publicvoidaddUser(Useruser){Connectionconn=null;PreparedStatementst=null;try{conn=JdbcUtils.getConnection();Stringsql="insertintouser(id,name,birthday,money)values(?,?,?,?)";st=conn.prepareStatement(sql);st.setInt(1,user.getId());st.setString(2,user.getName());//日期格式的转换(utils.date转化成sql.date)st.setDate(3,newDate(user.getBirthday().getTime()));st.setFloat(4,user.getMoney());intcount=st.executeUpdate();System.out.println("添加记录条数:"+count);}catch(Exceptione){thrownewDaoException(e.getMessage(),e);}finally{JdbcUtils.free(null,st,conn);}}/***删除用户*/publicintdelete(Useruser){Connectionconn=null;PreparedStatementst=null;try{conn=JdbcUtils.getConnection();Stringsql="deletefromuserwhereid=?";st=conn.prepareStatement(sql);st.setInt(1,user.getId());intcount=-1;count=st.executeUpdate();System.out.println("删除记录条数:"+count);returncount;}catch(Exceptione){thrownewDaoException(e.getMessage(),e);}finally{JdbcUtils.free(null,st,conn);}}/***通过userId获取用户信息*/publicUsergetUserById(intuserId){Connectionconn=null;PreparedStatementst=null;ResultSetrs=null;try{conn=JdbcUtils.getConnection();Stringsql="select*fromuserwhereid=?";st=conn.prepareStatement(sql);st.setInt(1,userId);rs=st.executeQuery();if(rs.next()){Useruser=newUser();user.setId(userId);user.setName(rs.getString("name"));user.setBirthday(rs.getDate("birthday"));user.setMoney(rs.getFloat("money"));returnuser;}}catch(Exceptione){thrownewDaoException(e.getMessage(),e);}finally{JdbcUtils.free(rs,st,conn);}returnnull;}/***更新用户信息*/publicintupdate(Useruser){Connectionconn=null;PreparedStatementst=null;try{conn=JdbcUtils.getConnection();Stringsql="updateusersetname=?,birthday=?,money=?whereid=?";st=conn.prepareStatement(sql);st.setString(1,user.getName());st.setDate(2,newDate(user.getBirthday().getTime()));st.setFloat(3,user.getMoney());st.setInt(3,user.getId());intcount=0;count=st.executeUpdate();System.out.println("更新的记录数:"+count);returncount;}catch(Exceptione){thrownewDaoException(e.getMessage(),e);}finally{JdbcUtils.free(null,st,conn);}}}
package com.weijia.domain;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import com.weijia.firstdemo.JdbcUtils;public class UserDaoImpl implements UserDao{/** * 添加用户 */public void addUser(User user) {Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "insert into user(id,name,birthday,money) values(?,?,?,?)";st = conn.prepareStatement(sql);st.setInt(1,user.getId());st.setString(2,user.getName());//日期格式的转换(utils.date转化成sql.date)st.setDate(3,new Date(user.getBirthday().getTime()));st.setFloat(4, user.getMoney());int count = st.executeUpdate();System.out.println("添加记录条数:"+count);}catch(Exception e){throw new DaoException(e.getMessage(),e);}finally{JdbcUtils.free(null, st, conn);}}/** * 删除用户 */public int delete(User user) {Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "delete from user where id=?";st = conn.prepareStatement(sql);st.setInt(1,user.getId());int count = -1;count = st.executeUpdate();System.out.println("删除记录条数:"+count);return count;}catch(Exception e){throw new DaoException(e.getMessage(),e);}finally{JdbcUtils.free(null, st, conn);}}/** * 通过userId获取用户信息 */public User getUserById(int userId) {Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();String sql = "select * from user where id=?";st = conn.prepareStatement(sql);st.setInt(1,userId);rs = st.executeQuery();if(rs.next()){User user = new User();user.setId(userId);user.setName(rs.getString("name"));user.setBirthday(rs.getDate("birthday"));user.setMoney(rs.getFloat("money"));return user;}}catch(Exception e){throw new DaoException(e.getMessage(),e);}finally{JdbcUtils.free(rs, st, conn);}return null;}/** * 更新用户信息 */public int update(User user){Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "update user set name=?,birthday=?,money=? where id=?";st = conn.prepareStatement(sql);st.setString(1,user.getName());st.setDate(2,new Date(user.getBirthday().getTime()));st.setFloat(3,user.getMoney());st.setInt(3,user.getId());int count = 0;count = st.executeUpdate();System.out.println("更新的记录数:"+count);return count;}catch(Exception e){throw new DaoException(e.getMessage(),e);}finally{JdbcUtils.free(null, st, conn);}}}
然后是Servic层：
[java] view plaincopyprint?packagecom.weijia.domain;publicclassUserService{privateUserDaouserDao;publicUserService(){//通过工厂实例化UserDao对象userDao=DaoFactory.getInstance().createUserDao();System.out.println("userDao:"+userDao);}/***注册用户*@paramuser*/publicvoidregist(Useruser){if(user==null){System.out.println("注册信息无效!!");}else{userDao.addUser(user);}}/***查询用户*@paramuserId*@return*/publicUserquery(intuserId){Useruser=userDao.getUserById(userId);if(user==null){System.out.println("查询结果为空!!");}else{System.out.println(user.getId()+"\t"+user.getName()+"\t"+user.getBirthday()+"\t"+user.getMoney());}returnuserDao.getUserById(userId);}/***更新用户*@paramuser*/publicvoidupdate(Useruser){if(user.getId()<=0){System.out.println("用户id无效,无法更新");}else{userDao.update(user);}}/***删除用户*@paramuser*/publicvoiddelete(Useruser){if(user.getId()<=0){System.out.println("用户id无效,无法删除!!");}else{userDao.delete(user);}}}
package com.weijia.domain;public class UserService {private UserDao userDao;public UserService(){//通过工厂实例化UserDao对象userDao = DaoFactory.getInstance().createUserDao();System.out.println("userDao:"+userDao);}/** * 注册用户 * @param user */public void regist(User user){if(user == null){System.out.println("注册信息无效!!");}else{userDao.addUser(user);}}/** * 查询用户 * @param userId * @return */public User query(int userId){User user = userDao.getUserById(userId);if(user == null){System.out.println("查询结果为空!!");}else{System.out.println(user.getId()+"\t"+user.getName()+"\t"+user.getBirthday()+"\t"+user.getMoney());}return userDao.getUserById(userId);}/** * 更新用户 * @param user */public void update(User user){if(user.getId()<=0){System.out.println("用户id无效,无法更新");}else{userDao.update(user);}}/** * 删除用户 * @param user */public void delete(User user){if(user.getId()<=0){System.out.println("用户id无效,无法删除!!");}else{userDao.delete(user);}}}
这里我们还需要额外的两个类：
一个是异常类，因为我们需要自定义我们自己的一个异常，这样方便进行捕获：
[java] view plaincopyprint?packagecom.weijia.domain;publicclassDaoExceptionextendsRuntimeException{privatestaticfinallongserialVersionUID=1L;publicDaoException(){}publicDaoException(Exceptione){super(e);}publicDaoException(Stringmsg){super(msg);}publicDaoException(Stringmsg,Exceptione){super(msg,e);}}
package com.weijia.domain;public class DaoException extends RuntimeException{private static final long serialVersionUID = 1L;public DaoException(){}public DaoException(Exception e){super(e);}public DaoException(String msg){super(msg);}public DaoException(String msg,Exception e){super(msg,e);}}
同时，我们这里面采用工厂模式进行实例化UserDao对象：
[java] view plaincopyprint?packagecom.weijia.domain;importjava.io.FileInputStream;importjava.util.Properties;publicclassDaoFactory{/***单例模式*/privatestaticUserDaouserDao=null;privatestaticDaoFactoryinstance=newDaoFactory();privateDaoFactory(){/***通过读取属性文件来动态的加载Dao层类*/Propertiesprop=newProperties();try{FileInputStreamfis=newFileInputStream("src/com/weijia/domain/daoconfig.properties");prop.load(fis);StringclassName=prop.getProperty("userDaoClass");Class<?>clazz=Class.forName(className);userDao=(UserDao)clazz.newInstance();fis.close();}catch(Throwablee){thrownewExceptionInInitializerError(e);}}publicstaticDaoFactorygetInstance(){returninstance;}publicUserDaocreateUserDao(){returnuserDao;}}
package com.weijia.domain;import java.io.FileInputStream;import java.util.Properties;public class DaoFactory {/** * 单例模式 */private static UserDao userDao = null;private static DaoFactory instance = new DaoFactory();private DaoFactory(){/** * 通过读取属性文件来动态的加载Dao层类 */Properties prop = new Properties();try{FileInputStream fis = new FileInputStream("src/com/weijia/domain/daoconfig.properties");prop.load(fis);String className = prop.getProperty("userDaoClass");Class<?> clazz = Class.forName(className);userDao = (UserDao)clazz.newInstance();fis.close();}catch(Throwable e){throw new ExceptionInInitializerError(e);}}public static DaoFactory getInstance(){return instance;}public UserDao createUserDao(){return userDao;}}
这里面是读取properties文件，然后去读取类名进行加载，这种方式是很灵活的
测试：
[java] view plaincopyprint?packagecom.weijia.domain;importjava.util.Date;publicclassTestDemo{publicstaticvoidmain(String[]args)throwsException{UserServiceuserService=newUserService();System.out.println("添加用户:");userService.regist(newUser(1,"jiangwei",newDate(System.currentTimeMillis()),300));}}
package com.weijia.domain;import java.util.Date;public class TestDemo {public static void main(String[] args) throws Exception{UserService userService = new UserService();System.out.println("添加用户:");userService.regist(new User(1,"jiangwei",new Date(System.currentTimeMillis()),300));}}
这里我们看到其实这些操作真的很简单，就是按照那样的几个步骤来操作即可，同时我们还需要将结构进行分层，以便管理，我们这里面测试的时候，撇开了创建数据库的一个环节，至于那个环节，也是不难的，可以从网上搜索一下即可。
Statement中的sql依赖注入的问题
接着来看一下关于我们上面的例子中使用了Statement进行操作的，其实这里面是存在一个问题的，就是会有sql注入的问题，我们先来看一下这个问题：
查询学生信息：
[java] view plaincopyprint?/***使用Statement读取数据*@