1、JDBC基础的查询

JDBC：Java Database Connection，Java数据库连接技术！

Dao层：数据访问层，database accesss object！

JDBC的操作口诀：CCSR！

C：Class.forName();//加载驱动类

C：获取Connection;//获取数据库连接对象Connection

S：获取语句分析容器Statement

R：获取数据库查询的结果集

例子：获取emp表的第一条数据

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class EmpDao {

         /**

          * 获取emp表的数据

          */

         public static void getEmps(){

                   try{

                            //C：加载驱动类

                            Class.forName("com.mysql.jdbc.Driver");

                            //C：获取数据库连接对象

                            Connection con =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/company","root","");

                            //S：获取语句分析容器（操作sql语句的容器）

                            Statement st = con.createStatement();

                            String sql = "select * from emp";

                            //R：执行sql得到结果集

                            ResultSet rs = st.executeQuery(sql);

                            //移动游标获取经过的行

                            rs.next();

                            //获取第一条数据

                            int empno = rs.getInt(1);

                            String ename = rs.getString(2);

                            String hiredate = rs.getString("hiredate");

                            System.out.println(empno + " " + ename + " " + hiredate);

                   }catch(Exception e){

                            e.printStackTrace();

                   }

         }

         public static void main(String[] args) {

                   getEmps();

         }

}

总结：JDBC的常见对象

Connection：数据库连接对象

Statement：语句分析容器

ResultSet：查询的结果集对象

例子：获取emp表的所有数据，并关闭资源

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class EmpDao {

         /**

          * 获取emp表的数据

          */

         public static void getEmps(){

                   try{

                            //C：加载驱动类

                            Class.forName("com.mysql.jdbc.Driver");

                            //C：获取数据库连接对象

                            Connection con =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/company","root","");

                            //S：获取语句分析容器（操作sql语句的容器）

                            Statement st = con.createStatement();

                            String sql = "select * from emp";

                            //R：执行sql得到结果集

                            ResultSet rs = st.executeQuery(sql);

                            //移动游标获取经过的行

                            while(rs.next()){

                                     //获取第一条数据 rs.getInt(1)：获取第一个字段的数据

                                     int empno = rs.getInt("empno");

                                     String ename = rs.getString("ename");

                                     String hiredate = rs.getString("hiredate");

                                     System.out.println(empno + " " + ename + " " + hiredate);

                            }

                            //关闭资源释放连接（倒过来关闭）

                            rs.close();

                            st.close();

                            con.close();

                   }catch(Exception e){

                            e.printStackTrace();

                   }

         }

         public static void main(String[] args) {

                   getEmps();

         }

}

注意：Java中资源类型的对象，都应该关闭，类似File（文件读取）、Scanner（文件、数据读取）、Connection、Statement、ResultSet！

2、优化JDBC代码，确保安全性

问题：如果我们的sql或者查询的过程导致异常，上述例子中关闭资源是不执行的，为了确保关闭资源的代码执行，我们需要在finally中尽心关闭资源的操作！

public class EmpDao {

         /**

          * 获取emp表的数据

          */

         public static void getEmps(){

                   //将Connection、Statement、ResultSet定义为生命周期更长的变量

                   Connection con = null;

                   Statement st = null;

                   ResultSet rs = null;

                   try{

                            //C：加载驱动类

                            Class.forName("com.mysql.jdbc.Driver");

                            //C：获取数据库连接对象

                            con =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/company","root","");

                            //S：获取语句分析容器（操作sql语句的容器）

                            st = con.createStatement();

                            String sql = "select * from emp";

                            //R：执行sql得到结果集

                            rs = st.executeQuery(sql);

                            //移动游标获取经过的行

                            while(rs.next()){

                                     //获取第一条数据 rs.getInt(1)：获取第一个字段的数据

                                     int empno = rs.getInt("empno");

                                     String ename = rs.getString("ename");

                                     String hiredate = rs.getString("hiredate");

                                     System.out.println(empno + " " + ename + " " + hiredate);

                            }

                   }catch(Exception e){

                            e.printStackTrace();

                   }finally{

                            try{

                                     //关闭资源释放连接（倒过来关闭）

                                     if(rs != null){

                                       rs.close();

                                     }

                                     if(st != null){

                                       st.close();

                                     }

                                     if(con != null){

                                       con.close();

                                     }

                            }catch(Exception e){

                                     e.printStackTrace();

                            }

                   }

         }

         public static void main(String[] args) {

                   getEmps();

         }

}

3、将查询结果封装为Map，再封装为List返回（sql作为方法的输入参数）

public class EmpDao {

    /**

     * 获取emp表的数据

     */

    public static List getEmps(String sql){

        List list = new ArrayList();

        //将Connection、Statement、ResultSet定义为生命周期更长的变量

        Connection con = null;

        Statement st = null;

        ResultSet rs = null;

        try{

            //C：加载驱动类

            Class.forName("com.mysql.jdbc.Driver");

            //C：获取数据库连接对象

            con =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/company","root","");

            //S：获取语句分析容器（操作sql语句的容器）

            st = con.createStatement();

            //R：执行sql得到结果集

            rs = st.executeQuery(sql);

            //移动游标获取经过的行

            while(rs.next()){

                //获取第一条数据 rs.getInt(1)：获取第一个字段的数据

                int empno = rs.getInt("empno");

                String ename = rs.getString("ename");

                String hiredate = rs.getString("hiredate");

                double sal = rs.getDouble("sal");

                String job = rs.getString("job");

                //生成Map封装每组参数

                Map map = new HashMap();

                map.put("empno", empno);

                map.put("ename", ename);

                map.put("hiredate", hiredate);

                map.put("job", job);

                map.put("sal", sal);

                //将map存放到List中

                list.add(map);

            }

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            try{

                //关闭资源释放连接（倒过来关闭）

                if(rs != null){

                  rs.close();

                }

                if(st != null){

                  st.close();

                }

                if(con != null){

                  con.close();

                }

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        return list;

    }

    public static void main(String[] args) {

        List list = getEmps("select * from emp where sal >= 4000");

        for(int i = 0;i < list.size();i++){

            Map map = (Map)list.get(i);

            System.out.println(map.get("empno") + " " + map.get("ename") + " " + map.get("sal"));

        }

    }

}

4、将查询结果封装为对象，再封装为List返回

首先提供一个Emp对象和数据库的emp表对应，为Emp对象提供和数据库的表的字段对应的属性，为属性提供get、set方法，有参、无参构造方法！

public class EmpDao {

    /**

     * 获取emp表的数据

     */

    public static List getEmps(String sql){

        List list = new ArrayList();

        //将Connection、Statement、ResultSet定义为生命周期更长的变量

        Connection con = null;

        Statement st = null;

        ResultSet rs = null;

        try{

            //C：加载驱动类

            Class.forName("com.mysql.jdbc.Driver");

            //C：获取数据库连接对象

            con =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/company","root","");

            //S：获取语句分析容器（操作sql语句的容器）

            st = con.createStatement();

            //R：执行sql得到结果集

            rs = st.executeQuery(sql);

            //移动游标获取经过的行

            while(rs.next()){

                //获取第一条数据 rs.getInt(1)：获取第一个字段的数据

                int empno = rs.getInt("empno");

                String ename = rs.getString("ename");

                String hiredate = rs.getString("hiredate");

                double sal = rs.getDouble("sal");

                String job = rs.getString("job");

                //生成Map封装每组参数

                Emp emp = new Emp(empno, ename, sal, job, hiredate);

                //将map存放到List中

                list.add(emp);

            }

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            try{

                //关闭资源释放连接（倒过来关闭）

                if(rs != null){

                  rs.close();

                }

                if(st != null){

                  st.close();

                }

                if(con != null){

                  con.close();

                }

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        return list;

    }

    public static void main(String[] args) {

        List list = getEmps("select * from emp where sal >= 4000");

        for(int i = 0;i < list.size();i++){

            Emp e = (Emp) list.get(i);

            System.out.println(e.getEmpno() + " " + e.getEname() + " " + e.getSal());

        }

    }

}

5、JDBC执行insert、delete、update语句

    /**

     * 执行增加、修改、删除语句

     */

    public static int changeData(String sql){

        int r = 0;

        Connection con = null;

        Statement st = null;

        try{

            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/company?characterEncoding=utf8", "root", "");

            st = con.createStatement();

            r = st.executeUpdate(sql);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            try{

                if(st != null){

                    st.close();

                }

                if(con != null){

                    con.close();

                }

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        return r;

    }

    public static void main(String[] args) {

//      String sql = "insert into emp(ename,sal,job) values('张小芳',5000,'开发')";

//      String sql = "update emp set comm=300 where ename='张小芳'";

        String sql = "delete from emp where ename='张小芳'";

        int x = changeData(sql);

        System.out.println(x);

    }

6、抽取ConnectionMgr类，并将数据库访问信息定义到properties文件

为了方便修改以及能够实现代码的复用，我们将数据库连接对象（Connection）的获取以及数据库资源的关闭（Connection、Statement、ResultSet）抽取为一个类ConnectionMgr！

EmpDao：

public class EmpDao {

    /**

     * 获取emp表的数据

     */

    public static List getEmps(String sql){

        List list = new ArrayList();

        //将Connection、Statement、ResultSet定义为生命周期更长的变量

        Connection con = null;

        Statement st = null;

        ResultSet rs = null;

        try{

            con = ConnectionMgr.getConnection();

            //S：获取语句分析容器（操作sql语句的容器）

            st = con.createStatement();

            //R：执行sql得到结果集

            rs = st.executeQuery(sql);

            //移动游标获取经过的行

            while(rs.next()){

                //获取第一条数据 rs.getInt(1)：获取第一个字段的数据

                int empno = rs.getInt("empno");

                String ename = rs.getString("ename");

                String hiredate = rs.getString("hiredate");

                double sal = rs.getDouble("sal");

                String job = rs.getString("job");

                //生成Map封装每组参数

                Emp emp = new Emp(empno, ename, sal, job, hiredate);

                //将map存放到List中

                list.add(emp);

            }

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            ConnectionMgr.closeResource(con, st, rs);

        }

        return list;

    }

    /**

     * 执行增加、修改、删除语句

     */

    public static int changeData(String sql){

        int r = 0;

        Connection con = null;

        Statement st = null;

        try{

            con = ConnectionMgr.getConnection();

            st = con.createStatement();

            r = st.executeUpdate(sql);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            ConnectionMgr.closeResource(con, st, null);

        }

        return r;

    }

    public static void main(String[] args) {

        String sql = "insert into emp(ename,sal,job) values('张小芳',5000,'开发')";

//      String sql = "update emp set comm=300 where ename='张小芳'";

//      String sql = "delete from emp where ename='张小芳'";

        int x = changeData(sql);

        System.out.println(x);

    }

}

ConnectionMgr：

/**

 * 获取数据库的连接以及关闭数据库的连接（释放资源）

 */

public class ConnectionMgr {

    /**

     * 获取数据库连接

     * @return

     * @throws ClassNotFoundException

     * @throws SQLException

     */

    public static Connection getConnection() throws ClassNotFoundException, SQLException{

        Connection con = null;

        String drivername = GetProperties.readValue("/dao_cfg.properties","drivername");

        String url = GetProperties.readValue("/dao_cfg.properties","url");

        String username = GetProperties.readValue("/dao_cfg.properties","username");

        String password = GetProperties.readValue("/dao_cfg.properties","password");

        //C：加载驱动类

        Class.forName(drivername);

        //C：获取数据库连接对象

        con =  DriverManager.getConnection(url,username,password);

        return con;

    }

    /**

     * 关闭数据库资源

     */

    public static void closeResource(Connection con,Statement st,ResultSet rs){

        try{

            //关闭资源释放连接（倒过来关闭）

            if(rs != null){

              rs.close();

            }

            if(st != null){

              st.close();

            }

            if(con != null){

              con.close();

            }

        }catch(Exception e){

            e.printStackTrace();

        }

    }

}

GetProperties：

public class GetProperties {

    /**

     * 读取properties文件的值

     * @param paramName

     * @return

     */

    public static String readValue(String fileName,String paramName){

        String r = null;

        InputStream is = null;

        Properties pro = null;

        try{

            is = GetProperties.class.getResourceAsStream(fileName);

            pro = new Properties();

            pro.load(is);

            r = pro.getProperty(paramName);

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            try{

                if(is != null){

                    is.close();

                }

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        return r;

    }

    public static void main(String[] args) {

        System.out.println(readValue("/dao_cfg.properties","url"));

    }

}

dao_cfg.properties：

drivername=com.mysql.jdbc.Driver

url=jdbc\:mysql\://127.0.0.1\:3306/company?characterEncoding\=utf8

username=root

password=