24H免费课程咨询  TEL:13401595960   QQ:1870218756  微信:13401595960(李老师)

东方博宜

网站首页 > 软件开发资讯 > Java开发

JDBC连接MySql数据库基础(一)

2017-08-25 20:46:51 东方博宜 阅读

1JDBC基础的查询

JDBCJava Database ConnectionJava数据库连接技术!

 

Dao层:数据访问层,database accesss object

JDBC的操作口诀:CCSR

CClass.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(文件、数据读取)、ConnectionStatementResultSet

 

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

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

 

public class EmpDao {

 

         /**

          * 获取emp表的数据

          */

         public static void getEmps(){

                   //ConnectionStatementResultSet定义为生命周期更长的变量

                   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();

        //ConnectionStatementResultSet定义为生命周期更长的变量

        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对象提供和数据库的表的字段对应的属性,为属性提供getset方法,有参、无参构造方法!

public class EmpDao {

 

    /**

     * 获取emp表的数据

     */

    public static List getEmps(String sql){

        List list = new ArrayList();

        //ConnectionStatementResultSet定义为生命周期更长的变量

        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());

        }

    }

}

 

5JDBC执行insertdeleteupdate语句

    /**

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

     */

    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)的获取以及数据库资源的关闭(ConnectionStatementResultSet)抽取为一个类ConnectionMgr

 

EmpDao

public class EmpDao {

 

    /**

     * 获取emp表的数据

     */

    public static List getEmps(String sql){

        List list = new ArrayList();

        //ConnectionStatementResultSet定义为生命周期更长的变量

        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=

 

image.png

Powered by 东方博宜教育咨询江苏有限公司  ©2008-2017 www.czos.cn