[转载]javaEE学习篇之——JDBC详解原文链接：http://blog.csdn.net/jiangwei0910410003/article/details/26164629
目录1.摘要2.JDBC的使用步骤 1.注册驱动 只做一次 2.建立连接Connection 3.创建执行SQL的语句Statement 4.处理执行结果ResultSet 5.释放资源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框架中的JdbcTemplate 1.加强版的JdbcTemplate 1.NamedParameterJdbcTemplate 2.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%20conn%20=%20DriverManager.getConnection(url,%20user,%20password);url格式：%20JDBC:子协议:子名称//主机名:端口/数据库名？属性名=属性值&…%20User,password可以用“属性名=属性值”方式告诉数据库；%20其他参数如：useUnicode=true&characterEncoding=GBK。
3.创建执行SQL的语句(Statement)[java]%20view%20plaincopyprint?StatementStatementst=conn.createStatement();st.executeQuery(sql);PreparedStatementStringsql=“select*fromtable_namewherecol_name=?”;PreparedStatementps=conn.preparedStatement(sql);ps.setString(1,“col_value”);ps.executeQuery();StatementStatement%20st%20=%20conn.createStatement();st.executeQuery(sql);PreparedStatementString%20sql%20=%20“select%20*%20from%20table_name%20where%20col_name=?”;PreparedStatement%20ps%20=%20conn.preparedStatement(sql);ps.setString(1,%20“col_value”);ps.executeQuery();4.处理执行结果(ResultSet)[java]%20view%20plaincopyprint?ResultSetrs=statement.executeQuery(sql);While(rs.next()){rs.getString(“col_name”);rs.getInt(“col_name”);//…}ResultSet%20rs%20=%20statement.executeQuery(sql);While(rs.next()){rs.getString(“col_name”);rs.getInt(“col_name”);//…}5.释放资源释放ResultSet,%20Statement,Connection.%20数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
下面来看一下完整的Demo:
工具类：JdbcUtils
[java]%20view%20plaincopyprint?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%20com.weijia.firstdemo;import%20java.sql.Connection;import%20java.sql.DriverManager;import%20java.sql.ResultSet;import%20java.sql.SQLException;import%20java.sql.Statement;import%20javax.sql.DataSource;public%20class%20JdbcUtils%20{private%20static%20String%20user%20=%20"root";private%20static%20String%20password%20=%20"123456";private%20static%20String%20dbName%20=%20"test";private%20static%20%20String%20url%20=%20"jdbc:mysql://localhost:3306/"+dbName+"?user="+user+"&password="+password+"&useUnicode=true&characterEncoding=gb2312";private%20static%20DataSource%20dataSource%20=%20null;/**%20*%20加载驱动%20*/static{try{Class.forName("com.mysql.jdbc.Driver");}catch(Exception%20e){System.out.println("Exception:"+e.getMessage()+"");throw%20new%20ExceptionInInitializerError(e);}}private%20JdbcUtils(){}/**%20*%20获取连接%20*%20@return%20*%20@throws%20SQLException%20*/public%20static%20Connection%20getConnection()%20throws%20SQLException{return%20DriverManager.getConnection(url);}public%20static%20DataSource%20getDataSource(){return%20dataSource;}/**%20*%20释放资源%20*%20@param%20rs%20*%20@param%20st%20*%20@param%20conn%20*/public%20static%20void%20free(ResultSet%20rs,Statement%20st,Connection%20conn){try{if(rs%20!=%20null){rs.close();}}catch(SQLException%20e){e.printStackTrace();}finally{try{if(st%20!=%20null){st.close();}}catch(SQLException%20e){e.printStackTrace();}finally{try{if(conn%20!=%20null){conn.close();}}catch(SQLException%20e){e.printStackTrace();}}}}}测试类：
[java]%20view%20plaincopyprint?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%20com.weijia.firstdemo;import%20java.sql.Connection;import%20java.sql.DriverManager;import%20java.sql.ResultSet;import%20java.sql.SQLException;import%20java.sql.Statement;public%20class%20Demo%20{public%20static%20void%20main(String[]%20args)%20throws%20Exception{//测试代码：test();//标准规范代码：template();}//模板代码public%20static%20void%20template(){Connection%20conn%20=%20null;Statement%20st%20=%20null;ResultSet%20rs%20=%20null;try%20{conn%20=%20JdbcUtils.getConnection();//创建语句st%20=%20conn.createStatement();//执行语句rs%20=%20st.executeQuery("select%20*%20from%20user");//处理结果while(rs.next()){System.out.println(rs.getObject(1)%20+%20"/t"%20+%20rs.getObject(2)%20+%20"/t"%20+%20rs.getObject(3)%20+%20"/t");}}catch(SQLException%20e){e.printStackTrace();}catch(Exception%20e){e.printStackTrace();}finally{JdbcUtils.free(rs,%20st,%20conn);}}//测试static%20void%20test()%20throws%20Exception{//注册驱动DriverManager.registerDriver(new%20com.mysql.jdbc.Driver());//通过系统属性来注册驱动System.setProperty("jdbc.drivers","");//静态加载驱动Class.forName("com.mysql.jdbc.Driver");//建立连接String%20url%20=%20"jdbc:mysql://localhost:3306";String%20userName%20=%20"root";String%20password%20=%20"";Connection%20conn%20=%20DriverManager.getConnection(url,userName,password);//创建语句Statement%20st%20=%20conn.createStatement();//执行语句ResultSet%20rs%20=%20st.executeQuery("select%20*%20from%20user");//处理结果while(rs.next()){System.out.println(rs.getObject(1)%20+%20"/t"%20+%20rs.getObject(2)%20+%20"/t"%20+%20rs.getObject(3)%20+%20"/t");}//释放资源rs.close();st.close();conn.close();}}注意：这里还要记住引入额外的jar.这个网上很多的，这里使用的是MySql,搜一下MySql驱动的jar就行了。这里我们将一些操作都放到一个工具类中，这种方式是很优雅的。
使用JDBC来实现CRUD的操作我们这里就采用分层操作：Dao层，Service层
首先看一下domain域中的User实体
[java]%20view%20plaincopyprint?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%20com.weijia.domain;import%20java.util.Date;public%20class%20User%20{private%20int%20id;private%20String%20name;private%20Date%20birthday;private%20float%20money;public%20User(){}public%20User(int%20id,String%20name,Date%20birthday,float%20money){this.id%20=%20id;this.name%20=%20name;this.birthday%20=%20birthday;this.money%20=%20money;}public%20int%20getId()%20{return%20id;}public%20void%20setId(int%20id)%20{this.id%20=%20id;}public%20String%20getName()%20{return%20name;}public%20void%20setName(String%20name)%20{this.name%20=%20name;}public%20Date%20getBirthday()%20{return%20birthday;}public%20void%20setBirthday(Date%20birthday)%20{this.birthday%20=%20birthday;}public%20float%20getMoney()%20{return%20money;}public%20void%20setMoney(float%20money)%20{this.money%20=%20money;}@Overridepublic%20String%20toString(){return%20"[id="+id+",name="+name+",birthday="+birthday+",money="+money+"]";}}再来看一下Dao层结构：
接口：
[java]%20view%20plaincopyprint?packagecom.weijia.domain;publicinterfaceUserDao{//添加用户publicvoidaddUser(Useruser);//通过userid查询用户,id是唯一的,所以返回的是一个userpublicUsergetUserById(intuserId);//更新用户信息publicintupdate(Useruser);//删除用户信息publicintdelete(Useruser);}package%20com.weijia.domain;public%20interface%20UserDao%20{//添加用户public%20void%20addUser(User%20user);//通过userid查询用户,id是唯一的,所以返回的是一个userpublic%20User%20getUserById(int%20userId);//更新用户信息public%20int%20update(User%20user);//删除用户信息public%20int%20delete(User%20user);}实现类：
[java]%20view%20plaincopyprint?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%20com.weijia.domain;import%20java.sql.Connection;import%20java.sql.Date;import%20java.sql.PreparedStatement;import%20java.sql.ResultSet;import%20com.weijia.firstdemo.JdbcUtils;public%20class%20UserDaoImpl%20implements%20UserDao{/**%20*%20添加用户%20*/public%20void%20addUser(User%20user)%20{Connection%20conn%20=%20null;PreparedStatement%20st%20=%20null;try{conn%20=%20JdbcUtils.getConnection();String%20sql%20=%20"insert%20into%20user(id,name,birthday,money)%20values(?,?,?,?)";st%20=%20conn.prepareStatement(sql);st.setInt(1,user.getId());st.setString(2,user.getName());//日期格式的转换(utils.date转化成sql.date)st.setDate(3,new%20Date(user.getBirthday().getTime()));st.setFloat(4,%20user.getMoney());int%20count%20=%20st.executeUpdate();System.out.println("添加记录条数:"+count);}catch(Exception%20e){throw%20new%20DaoException(e.getMessage(),e);}finally{JdbcUtils.free(null,%20st,%20conn);}}/**%20*%20删除用户%20*/public%20int%20delete(User%20user)%20{Connection%20conn%20=%20null;PreparedStatement%20st%20=%20null;try{conn%20=%20JdbcUtils.getConnection();String%20sql%20=%20"delete%20from%20user%20where%20id=?";st%20=%20conn.prepareStatement(sql);st.setInt(1,user.getId());int%20count%20=%20-1;count%20=%20st.executeUpdate();System.out.println("删除记录条数:"+count);return%20count;}catch(Exception%20e){throw%20new%20DaoException(e.getMessage(),e);}finally{JdbcUtils.free(null,%20st,%20conn);}}/**%20*%20通过userId获取用户信息%20*/public%20User%20getUserById(int%20userId)%20{Connection%20conn%20=%20null;PreparedStatement%20st%20=%20null;ResultSet%20rs%20=%20null;try{conn%20=%20JdbcUtils.getConnection();String%20sql%20=%20"select%20*%20from%20user%20where%20id=?";st%20=%20conn.prepareStatement(sql);st.setInt(1,userId);rs%20=%20st.executeQuery();if(rs.next()){User%20user%20=%20new%20User();user.setId(userId);user.setName(rs.getString("name"));user.setBirthday(rs.getDate("birthday"));user.setMoney(rs.getFloat("money"));return%20user;}}catch(Exception%20e){throw%20new%20DaoException(e.getMessage(),e);}finally{JdbcUtils.free(rs,%20st,%20conn);}return%20null;}/**%20*%20更新用户信息%20*/public%20int%20update(User%20user){Connection%20conn%20=%20null;PreparedStatement%20st%20=%20null;try{conn%20=%20JdbcUtils.getConnection();String%20sql%20=%20"update%20user%20set%20name=?,birthday=?,money=?%20where%20id=?";st%20=%20conn.prepareStatement(sql);st.setString(1,user.getName());st.setDate(2,new%20Date(user.getBirthday().getTime()));st.setFloat(3,user.getMoney());st.setInt(3,user.getId());int%20count%20=%200;count%20=%20st.executeUpdate();System.out.println("更新的记录数:"+count);return%20count;}catch(Exception%20e){throw%20new%20DaoException(e.getMessage(),e);}finally{JdbcUtils.free(null,%20st,%20conn);}}}然后是Servic层：
[java]%20view%20plaincopyprint?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%20com.weijia.domain;public%20class%20UserService%20{private%20UserDao%20userDao;public%20UserService(){//通过工厂实例化UserDao对象userDao%20=%20DaoFactory.getInstance().createUserDao();System.out.println("userDao:"+userDao);}/**%20*%20注册用户%20*%20@param%20user%20*/public%20void%20regist(User%20user){if(user%20==%20null){System.out.println("注册信息无效!!");}else{userDao.addUser(user);}}/**%20*%20查询用户%20*%20@param%20userId%20*%20@return%20*/public%20User%20query(int%20userId){User%20user%20=%20userDao.getUserById(userId);if(user%20==%20null){System.out.println("查询结果为空!!");}else{System.out.println(user.getId()+"/t"+user.getName()+"/t"+user.getBirthday()+"/t"+user.getMoney());}return%20userDao.getUserById(userId);}/**%20*%20更新用户%20*%20@param%20user%20*/public%20void%20update(User%20user){if(user.getId()<=0){System.out.println("用户id无效,无法更新");}else{userDao.update(user);}}/**%20*%20删除用户%20*%20@param%20user%20*/public%20void%20delete(User%20user){if(user.getId()<=0){System.out.println("用户id无效,无法删除!!");}else{userDao.delete(user);}}}这里我们还需要额外的两个类：
一个是异常类，因为我们需要自定义我们自己的一个异常，这样方便进行捕获：
[java]%20view%20plaincopyprint?packagecom.weijia.domain;publicclassDaoExceptionextendsRuntimeException{privatestaticfinallongserialVersionUID=1L;publicDaoException(){}publicDaoException(Exceptione){super(e);}publicDaoException(Stringmsg){super(msg);}publicDaoException(Stringmsg,Exceptione){super(msg,e);}}package%20com.weijia.domain;public%20class%20DaoException%20extends%20RuntimeException{private%20static%20final%20long%20serialVersionUID%20=%201L;public%20DaoException(){}public%20DaoException(Exception%20e){super(e);}public%20DaoException(String%20msg){super(msg);}public%20DaoException(String%20msg,Exception%20e){super(msg,e);}}同时，我们这里面采用工厂模式进行实例化UserDao对象：
[java]%20view%20plaincopyprint?packagecom.weijia.domain;importjava.io.FileInputStream;importjava.util.Properties;publicclassDaoFactory{/***单例模式*/privatestaticUserDaouserDao=null;privatestaticDaoFactoryinstance=newDaoFactory();privateDaoFactory(){