JDBC重点代码

JDBC： java 提供的用java代码操作数据库的一套编程接口规范,具体实现类由各个数据库厂商自己提供.(Mysql举例)
配置文件:p.properties
url=”jdbc:mysql://127.0.0.1:3306/db1”
user=root
password=root
driver=”com.mysql.jdbc.Driver”

    1. 快速入门
        a 导包
        b 注册驱动 
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://127.0.0.1:3306/db1";
        String user=root;
        String password=root;
        Connection conn = DriverManager.getConnection(url,user,password);
        String sql = "update dept set name = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,"jack");
        ps.setInt(2,10);
        int count = ps.executeUpdate();
        sout(count);
        String sql2 = "select * from dept where id =?";
        PreparedStatement ps2 = conn.prepareStatement(sql2);
        ps2.setInt(1,3);
        ResultSet rs = ps2.executeQuery();
        //....

    2. 工具类
        public class Utils{
            private static String url;
            private static String user;
            private static String password;
            private static String driver;
            static{
            //配置文件放在Moudle的src文件目录下
                Properties pro = new Properties();
                pro.load(Utils.class.getClassLoader()
                .getResourceAsStream("p.properties"));
                url = pro.getProperties("url");
                user = pro.getProperties("user");
                password = pro.getProperties("password");
                driver = pro.getProperties("driver");
                Class.forName(driver);
            }
            public static getConnection(){
                return DriverManager.getConnection(url,user,password);
            }
            public static void close(ResultSet rs, PreparedStatement ps
            , Connection conn){
                if(rs!=null){
                    rs.close();
                }
                if(ps!=null){
                    ps.close();
                }
                f(conn != null){
                    conn.close();
                }
            }
        }

    3.  工具类简化书写
             Connection conn = null;
             PreparedStatement ps = null;
             ResultSet rs = null;
             try{

                conn = Utils.getConnection();
                String sql = "select * from dept";
                ps =  conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while(rs.next()){
                    //...
                }
             }catch(Exception e ){
                 e.printStackTrace();
             }finally{
                 Utils.close(rs,ps,conn);
             }

    4. JDBC 事务处理  增删改数据库操作
             Connection conn = null;
             PreparedStatement ps1 = null;
             PreparedStatement ps2 = null;
             try{
                 conn = Utils.getConnection();
                 //开启事务
                 conn.setAutoCommit(false);
                 String sql1 = "update dept set money = money -? where id =?";
                 String sql2 = "update dept set money = money +? where id =?";
                 ps1 = conn.prepareStatement(sql1);
                 ps2 = conn.prepareStatement(sql2);
                 ps1.setInt(1,500);
                 ps1.setInt(2,1);
                 ps2.setInt(1,500);
                 ps2.setInt(2,2);
                 ps1.executeUpdate();
                 //制造异常
                 int b = 3/0;
                 ps2.executeUpdate();
                 //提交事务
                 conn.commit();
             }catch(Exeception e ){
                 if(conn != null){
                     try{
                         conn.rollback();
                     }catch(SQLException e1){
                         e1.printStackTrace();
                     }
                 }
                 e.printStackTrace();
             }finally{
                 Utils.close(null,ps,conn);
             }

    5. 连接池DataSource 
            厂商： C3P0/Druid
            C3P0:(配置文件自动导入驱动Driver 无需手动导入)
                DataSource ds = new CombopooledDataSource();
                Connection conn = null;
                PreparedStatement ps = null;
                ResultSet rs = null;
                try{
                    conn = ds.getConnection();
                    String sql = "select * from dept";
                    ps = conn.prepareStatement(sql);
                    rs = ps.executeQuery();
                    while(rs.next()){
                        //...
                    }
                }catch(Exception e){
                    e.printStackTrace();                                                                        
                }finally{
                //...释放资源
                }

            Druid:(配置文件是properties,需要手动导入)
            先写一个工具类
            public class Utils{
                private DataSource ds;
                static{
                    Properties pro = new Properties();
                    pro.load(Utils.class.getClassLoader().getResourceAsStream("p.properties"));
                    ds = DruidDataSourceFactory.creatDataSource(pro);
                }
                public static Connection getConn(){
                    return ds.getConnection();
                }
                public static DataSource getDs(){
                    return ds;
                }
            }

            public class Demo{
                public static void main(String[] args){
                    Connection conn = null;
                    PreparedStatement ps = null;
                    ResultSet rs = null;
               try{
                    conn = Utils.getConn();
                    String sql = "select * from dept";
                    ps = conn.prepareStatement(sql);
                    rs = ps.executeQuery();
                    while(rs.next()){
                        //...
                    }
                }catch(Exception e){
                    e.printStackTrace();
                }finally{
                    Utils.close(rs,ps,conn);
                }
            }
        }

    6.Spring框架中的 JdbcTemplate
        DataSource ds = Utils.getDs();
        JdbcTemplate t = new JdbcTemplate(ds);
        String sql = "update dept set balance =? whrer id = ? ";
         //修改数据
        t.update(sql,1000,3);
        //查询数据
        String sql2 = "select * from dept";
        // template.queryForMap()
        Map<E,T> map = t.queryForMap(sql2);
        sout(map.size()); // 长度为1 
        // template.queryForList()
        List<Map<E,T>> list = t.queryForList(sql2);
        // template.query()    EMP是JavaBean标准类
        List<EMP> myList = t.query(sql2, new BeanPropertyRowMapper<EMP>   
              (EMP.class));
        for(EMP emp : myList){
            System.out.println(emp);
        }
        // template.queryForObject() 查询聚合函数,num为结果
        int num = t.queryForObject(sql2,int.class);