关于JDBC的一些基础知识

JDBC     JDBC4驱动加载问题           JDK1.6之后JDBC就升级到了JDBC4，只要数据库生产商实现了JDBC4提供的接口，则在连接数据时，不需要显式加载驱动，系统自动加载          mysql驱动从5.1开始支持jdbc4      关于URL语法：jdbc:子协议://地址:端口号/数据库实例名 MySQL:jdbc:mysql://127.0.0.1:3306/test sqlserver: jdbc:sqlserver://127.0.0.1:1433;databaseName=test oracle: jdbc:oracle:thin:@127.0.0.1:1521:orcl      JDBC连接数据库： public static void main(String[] args) throws ClassNotFoundException, SQLException {            //1.加载数据库提供商提供的驱动            Class.forName("com.mysql.jdbc.Driver");            //DriverManager.registerDriver(new Driver());            //2.获取数据库连接对象            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb", "root", "123456");          //Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&&password=123456");            System.out.println(conn);            //3.获取处理命令（用于处理SQL语句）            Statement stmt = conn.createStatement();            //4.发送执行sql命令            //boolean f = stmt.execute("create table people(name varchar(16),id int ,sex varchar(8));");            stmt.execute("insert into people values('吴为傻逼',1,'20')");            //5.处理执行结果            //System.out.println(f);            //6.回收资源            stmt.close();            conn.close();       execute,executeUpdate,executeQuery区别？       excute用于执行任何的sql语句，返回布尔类型结果，如果被执行的sql语句为查询相关语句则会有ResultSet产生，从而返回true；其他非查询操作都会返回false      executeUpdate主要用于执行DML语句中的insert，delete，update操作，同时可以执行DDl（建表，建库）相关操作，如果执行DML中增删改操作时会返回操作影响的数据行数，如果是DDL操作则返回0. JDBC实现增删改查：（Statement为不安全对象，容易产生sql注入） public class JdbcCrud {      /**       * 向表中添加数据       */      public void insert(String name, int id, String sex) {            Connection conn = null;            Statement stmt = null;            try {                 // 加载驱动                 Class.forName("com.mysql.jdbc.Driver");                 // 获取连接                 conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb", "root", "123456");                 // 创建处理命令                 stmt = conn.createStatement();                 // 执行sql语句                 int i = stmt.executeUpdate("insert into people(name,id,sex) values('" + name + "','" + id + "','" + sex + "')");                 //处理结果                 if(i > 0)                 {                      System.out.println("添加成功，影响数据行数："+i);                 }            } catch (ClassNotFoundException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();            } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();            }finally{                 try {                      if(stmt != null) stmt.close();                      if(conn != null) conn.close();                 } catch (SQLException e) {                      // TODO Auto-generated catch block                      e.printStackTrace();                 }            }      }            //查询所有操作      public void queryAll()      {            Connection conn = null;            Statement stmt = null;            ResultSet rs = null;            try {                 Class.forName("com.mysql.jdbc.Driver");                 conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&&password=123456");                 stmt = conn.createStatement();                                  //执行查询操作获取结果集                 rs = stmt.executeQuery("select * from people");                 //取出结果集中的数据                 while(rs.next()){                      String name = rs.getString("name");                      int id = rs.getInt("id");                      String sex = rs.getString("sex");                                            System.out.println(name+"-----"+id+"-----"+sex);                 }            } catch (ClassNotFoundException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();            } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();            }finally{                 try {                      if(rs!=null)rs.close();                      if(stmt!=null)stmt.close();                      if(conn!=null)conn.close();                 } catch (SQLException e) {                      // TODO Auto-generated catch block                      e.printStackTrace();                 }            }      }            //根据用户id删除指定用户            public void deleteById(int id)      {            Connection conn = null;            Statement stmt = null;            try {                 Class.forName("com.mysql.jdbc.Driver");                 conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&&password=123456");                 stmt = conn.createStatement();                 int i = stmt.executeUpdate("delete from people where id =" + id );                 System.out.println("删除成功，影响数据行数："+i);            } catch (ClassNotFoundException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();            } catch (SQLException e) {                 // TODO Auto-generated catch block                 e.printStackTrace();            }finally {                 try {                      if(stmt!=null)stmt.close();                      if(conn!=null)conn.close();                 } catch (SQLException e) {                      // TODO Auto-generated catch block                      e.printStackTrace();                 }            }      } }   PreparedStatement（可防止SQL注入）      public  class  JdbcSafe {       public   void   add(String   name , int   id ,String   password )      {            Connection   conn   =   null ;            PreparedStatement   ps   =   null ;                         try   {                 Class. forName ( "com.mysql.jdbc.Driver" );                  conn   = DriverManager. getConnection ( "jdbc:mysql://127.0.0.1:3306/mydb" ,   "root" ,   "123456" );                  ps   =   conn .prepareStatement( "insert into tbuser(name,id,password) values(?,?,?)" );                  ps .setString(1,   name );                  ps .setInt(2,   id );                  ps .setString(3,   password );                                   int   i   = ps .executeUpdate();                 System. out .println( "影响记录行数：" + i );            }   catch   (ClassNotFoundException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            }   catch   (SQLException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            } finally {                  try   {                       ps .close();                       conn .close();                 }   catch   (SQLException   e ) {                       //   TODO   Auto-generated catch block                       e .printStackTrace();                 }            }      } JDBC连接封装和配置文件 //配置文件 #### mysql  connection info#### driver =   com.mysql.jdbc.Driver url =   jdbc:mysql://127.0.0.1:3306/ mydb user =   root password =  123456 public  class  DBConnection {             //声明连接数据库的基本参数       private   static   String   DRIVER   =   "com.mysql.jdbc.Driver" ;       private   static   String   URL   =   "jdbc:mysql://127.0.0.1:3306/mydb" ;       private   static   String   USER   =   "root" ;       private   static   String   PASSWORD   =   "123456" ;             //在静态块中加载驱动，防止反复加载       static {             try   {                  //获取属性文件                 Properties   pros   = System. getProperties ();                  //加载指定属性文件                  pros .load( new   FileInputStream( "src/jdbc.properties" ));                  DRIVER   =   pros .getProperty( "driver" );                  URL   =   pros .getProperty( "url" );                  USER   =   pros .getProperty( "user" );                  PASSWORD   =   pros .getProperty( "password" );                                  Class. forName ( DRIVER );            }   catch   (ClassNotFoundException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            }   catch   (FileNotFoundException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            }   catch   (IOException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            }      }             //封装获取连接方法       public   static   Connection getConn()      {             try   {                  return   DriverManager. getConnection ( URL , USER , PASSWORD );            }   catch   (SQLException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            }             return   null ;      }             //封装资源回收的方法       public   static   void   close(ResultSet   rs ,Statement   stmt ,Connection   conn )      {             try   {                  if ( rs   !=   null ) rs .close();                  if ( stmt != null ) stmt .close();                  if ( conn != null ) conn .close();            }   catch   (SQLException   e ) {                  //   TODO   Auto-generated catch block                  e .printStackTrace();            }      }       } JDBC日期时间类型转换 public  void  add(String  content ,Date  lasttime )  throws  SQLException{                        Connection   conn   = DBConnection. getConn ();            PreparedStatement   ps   =   conn .prepareStatement( "insert into daily(content,lasttime) values(?,?)" );             ps .setString(1,   content );             ps .setTimestamp(2,   new   Timestamp( lasttime .getTime()) );             int   i   =   ps .executeUpdate();            System. out .println( i );      }             public   void   queryAll()   throws   SQLException{            Connection   conn   = DBConnection. getConn ();            PreparedStatement   ps   =   conn .prepareStatement( "select content,submittime,lasttime from daily" );            ResultSet   rs   =   ps .executeQuery();             while ( rs .next())            {                 String   content   =   rs .getString( "content" );                  Date   d1   =   rs .getTimestamp( "submittime" );                  Date   d2   =   rs .getTimestamp( "lasttime" );                 System. out .println( content + "--" + d1 + "--" + d2 );            }            } 简单封装：查询数据库中某个表中的所有数据 public  List<Object[]> query()  throws  SQLException{            PreparedStatement   ps   = DBConnection. getConn ().prepareStatement( "select * from department limit 0,10" );            ResultSet   rs   =   ps .executeQuery();            ResultSetMetaData   rsmd   =   rs .getMetaData();             int   count   =   rsmd .getColumnCount();            List<Object[]>   list   =   new   ArrayList<>();             while ( rs .next()){                  //每读取到一行记录声明一个数组                 Object[]   objs   =   new   Object[ count ];                  for   ( int   i   = 1;   i   <=   count ;   i ++) {                       //获取列标签名(别名，若不存在则用列名)                      String   label   =   rsmd .getColumnLabel( i );                       //获取列名称                      String   cname   =   rsmd .getColumnName( i );                       //获取列数据类型       //               int   type = rsmd.getColumnType(i);       //              if(type == java.sql.Types.INTEGER){       //                    int   num   = rs.getInt(label);       //                   System.out.print( num +"  ");       //              }else if(type == java.sql.Types.VARCHAR){       //                   String value = rs.getString(label);       //                   System.out.print(value+"  ");       //              }                       //获取一列数据                      Object   obj   =   rs .getObject( label );                       //将列数据装入数组中                       objs [ i -1] =   obj ;                 }                  //将装有多列数据的一行结果装入集合                  list .add( objs );            }             return   list ;      } JDBC存储过程调用 //不带参数的存储过程调用 Connection  conn  = DBConnection. getConn (); CallableStatement cs = conn.prepareCall( "{call pro_01}" ); //带参数的存储过程调用（分页） //DTO public  class  PageUtils {       private   int   currentPage ;    //当前页       private   int   pageSize ;       //每页大小       private   String   tableName ;   //表名称       private   String   selections ;  //查询列          private   String   condition ;   //查询条件       private   String   sortColumn ;  //排序列       private   String   sortType ;    //排序类型   asc   desc             private   int   totalNum ;       //总记录数       private   int   totalPage ;           //总页码数       private   List<Object[]>   datas ;    //当前页数据             public   int   getCurrentPage() {             return   currentPage ;      }       public   void   setCurrentPage( int   currentPage ) {             this . currentPage   =   currentPage ;      }       public   int   getPageSize() {             return   pageSize ;      }       public   void   setPageSize( int   pageSize ) {             this . pageSize   =   pageSize ;      }       public   String getTableName() {             return   tableName ;      }       public   void   setTableName(String   tableName ) {             this . tableName   =   tableName ;      }       public   String getSelections() {             return   selections ;      }       public   void   setSelections(String   selections ) {             this . selections   =   selections ;      }       public   String getCondition() {             return   condition ;      }       public   void   setCondition(String   condition ) {             this . condition   =   condition ;      }       public   String getSortColumn() {             return   sortColumn ;      }       public   void   setSortColumn(String   sortColumn ) {             this . sortColumn   =   sortColumn ;      }       public   String getSortType() {             return   sortType ;      }       public   void   setSortType(String   sortType ) {             this . sortType   =   sortType ;      }       public   int   getTotalNum() {             return   totalNum ;      }       public   void   setTotalNum( int   totalNum ) {             this . totalNum   =   totalNum ;      }       public   int   getTotalPage() {             return   totalPage ;      }       public   void   setTotalPage( int   totalPage ) {             this . totalPage   =   totalPage ;      }       public   List<Object[]> getDatas() {             return   datas ;      }       public   void   setDatas(List<Object[]>   datas ) {             this . datas   =   datas ;      }       } public  PageUtils procPaging(PageUtils  pu )  throws  SQLException{            CallableStatement   cs   = getConn().prepareCall( "{call sp_paging(?,?,?,?,?,?,?,?,?)}" );             cs .setInt(1,   pu .getCurrentPage());             cs .setInt(2,   pu .getPageSize());             cs .setString(3,   pu .getTableName());             cs .setString(4,   pu .getSelections());             cs .setString(5,   pu .getCondition());             cs .setString(6,   pu .getSortColumn());             cs .setString(7,   pu .getSortType());                         //注册输出参数             cs .registerOutParameter(8, java.sql.Types. INTEGER );             cs .registerOutParameter(9, java.sql.Types. INTEGER );             //执行存储过程             cs .execute();             //获取制定位置的输出参数值             int   totalNum   =   cs .getInt(8);             int   totalPage   =   cs .getInt(9);             pu .setTotalNum( totalNum );             pu .setTotalPage( totalPage );                         //声明用于存储查询结果的集合            List<Object[]>   datas   =   new   ArrayList<>();                         //获取查询的结果集            ResultSet   rs   =   cs .getResultSet();            ResultSetMetaData   rsmd   =   rs .getMetaData();             int   count   =   rsmd .getColumnCount();             while ( rs .next()){                 Object[]   obj   =   new   Object[ count ];                  for ( int   i   = 0; i < count ; i ++){                       //获取标签名称（可能是列名称）                      String   label   =   rsmd .getColumnLabel( i +1);                      Object   c   =   rs .getObject( label );                       obj [ i ] =   c ;                 }                  datas .add( obj );            }             //将查询结果设置到PageUtils中             pu .setDatas( datas );             return   pu ;      } DTO：（ Data Transfer Object ） 数据传输对象，用于在前后端（界面，数据库）之间进行数据传递     1.临时存储界面提交的数据，并将数据通过jdbc加入到数据库中（数据持久化）     2.取出数据库表中的数据，临时存储到对象，并运转到界面端展示 public void add(Emp emp) throws SQLException{         String sql = "insert into employee(name,sex,tel,addr,email,zip,depno,birth) values(?,?,?,?,?,?,?,?)";         PreparedStatement ps = getConn().prepareStatement(sql);         ps.setString(1,emp.getName());         ps.setString(2,emp.getSex());         ps.setString(3,emp.getTel());         ps.setString(4,emp.getAddr());         ps.setString(5,emp.getEmail());         ps.setString(6,emp.getZip());         ps.setInt(7, emp.getDepno());         ps.setDate(8, new java.sql.Date(emp.getBirth().getTime()));         int i = ps.executeUpdate();         System.out.println("执行结果:"+i);     } public List<Emp> findAll() throws SQLException{         List<Emp> list = new ArrayList<>();         String sql = "select num,name,sex,tel,addr,email,zip,depno,birth from employee limit 0,10";         PreparedStatement ps = getConn().prepareStatement(sql);         ResultSet rs = ps.executeQuery();         Emp emp = null;         while(rs.next()){             int num = rs.getInt("num");             String name = rs.getString("name");             String addr = rs.getString("addr");             String email = rs.getString("email");             String sex = rs.getString("sex");             String tel = rs.getString("tel");             String zip = rs.getString("zip");             int depno = rs.getInt("depno");             Date birth = rs.getDate("birth");                          emp = new Emp(name, addr, zip, email, tel, sex, birth, depno);             emp.setNum(num);             list.add(emp);         }         return list;     } 分层思想：     视图层     业务逻辑层     数据持久层 DAO：（Data Access Object） 数据访问对象，负责对数据库进行CRUD相关的访问操作，内部的每一个方法都是一个对于数据库的原子操作 public class UserDAO extends BaseConn {     PreparedStatement ps;     ResultSet rs;     // 添加操作     public boolean insert(User user) throws SQLException {         ps = getConn().prepareStatement("insert into tbuser(username,password) values(?,?)");         ps.setString(1, user.getUsername());         ps.setString(2, user.getPassword());         int i = ps.executeUpdate();         return i > 0 ? true : false;     }     // 删除操作     public boolean delete(User user) throws SQLException {         ps = getConn().prepareStatement("delete from tbuser where id=?");         ps.setInt(1, user.getId());         int i = ps.executeUpdate();         return i > 0 ? true : false;     }     // 修改操作     public boolean update(User user) throws SQLException {         ps = getConn().prepareStatement("update tbuser set password=? where id=?");         ps.setString(1, user.getPassword());         ps.setInt(2, user.getId());         int i = ps.executeUpdate();         return i > 0 ? true : false;     }     // 查询所有     public List<User> findAll() throws SQLException {         List<User> list = new ArrayList<>();         ps = getConn().prepareStatement("select * from tbuser");         rs = ps.executeQuery();         User user = null;         while(rs.next()){             user = new User(rs.getInt("id"),rs.getString("username"), rs.getString("password"));             list.add(user);         }         return list;     }     // 根据id查询     public User findById(int id) throws SQLException {         ps = getConn().prepareStatement("select * from tbuser where id=?");         ps.setInt(1, id);         rs = ps.executeQuery();         User user = null;         if(rs.next()){             user = new User(id,rs.getString("username"), rs.getString("password"));         }         return user;     } } JDBC存储大文件 //存文件 public   void   insertClob( File   file )   throws   SQLException ,   IOException {             Connection   conn   =   DBUtils .getConn();             //获取文件字符输入流             FileReader   br   =   new   FileReader ( file );                         PreparedStatement   ps   =   conn .prepareStatement( "insert into tb_clob(fname,content) values(?,?)" );             ps .setString(1,   file .getName());             ps .setCharacterStream(2, br );             if ( ps .executeUpdate() > 0 ){                 System. out .println( "写入完成！" );            }             ps .close();             br .close();             conn .close();      } //取出文件 public   void   selectClob( int   id )   throws   SQLException      {             Connection   conn   =   DBUtils .getConn();             PreparedStatement   ps   =   conn .prepareStatement( "select content from tb_clob where id=?" );             ps .setInt(1,   id );             ResultSet   rs   =   ps .executeQuery();             if ( rs .next()){                 String   content   =   rs .getString( "content" ); //              Reader reader = rs.getCharacterStream("content"); //如果需要将数据存储到文件中，则获取字符流                 System. out .println( content );            }      } //存二进制文件 如果向MySQL中存储二进制文件时，数据库的字符集不能设置为GBK，要设置成UTF8 public   void   insertBlob( File   file )   throws   SQLException ,   IOException      {             Connection   conn   =   DBUtils .getConn();             //获取文件的字节输入流             FileInputStream   fis   =   new   FileInputStream ( file );             PreparedStatement   ps   =   conn .prepareStatement( "insert into tb_blob(fname,file) values(?,?)" );             ps .setString(1,   file .getName());             ps .setBinaryStream(2,   fis );   //设置二进制流             if ( ps .executeUpdate() > 0){                 System. out .println( "文件写入完成！" );            }             ps .close();             fis .close();             conn .close();      } //取二进制文件 public   void   selectBlob( int   id , File   dir )   throws   SQLException ,   IOException {             Connection   conn   =   DBUtils .getConn();             PreparedStatement   ps   =   conn .prepareStatement( "select fname,file from tb_blob where id=?" );             ps .setInt(1,   id );             ResultSet   rs   =   ps .executeQuery();             if ( rs .next()){                  //获取文件名                 String   fname   =   rs .getString( "fname" );                  //获取文件的二进制流(输入)                  InputStream   is   =   rs .getBinaryStream( "file" );                  //将文件名和目录组合为目标文件,并创建输出流                  FileOutputStream   fos   =   new   FileOutputStream ( new   File ( dir , fname ));                                   BufferedInputStream   bis   =   new   BufferedInputStream ( is );                  BufferedOutputStream   bos   =   new   BufferedOutputStream ( fos );                  byte []   b   =   new   byte [1024];                  int   len   = 0;                  while (( len   =   bis .read( b )) != -1)                 {                       bos .write( b ,0, len );                 }                  bos .close();                  bis .close();            }             rs .close();             ps .close();             conn .close();      }