JDBC连接MySQL数据库由浅入深解析

**

JDBC连接MySQL数据库由浅入深解析

**
一、标准的开发步骤：
1. 注册驱动.
2. 获得连接.
3. 获得语句执行平台
4. 执行sql语句
5. 处理结果
6. 释放资源.

---

准备数据库：

#创建数据库
create database mydb;
#使用数据库
use mydb;
###创建分类表
create table users(
  id int PRIMARY KEY AUTO_INCREMENT,
  username varchar(20),
  password varchar(20),
  salary varchar(20)
);
#初始化数据
insert into users values(null,'张三','123',2000);
insert into users values(null,'李四','456',3000);
insert into users values(null,'王五','789',6000);

1.1.1 标准代码实现

public class JDBCDemo2 {
    @Test
    /**
     * 查询数据
     */
    public void demo1() {
        /*
         * 1. 注册驱动.
         * 2. 获取连接.
         * 3. 获取可以执行SQL语句的对象.
         * 4. 执行SQL语句, 获取结果集.
         * 5. 操作结果集.
         * 6. 释放资源.
         */
        Connection conn = null;
        Statement stat = null;
        ResultSet rs =  null;
        try {
            //1
            Class.forName("com.mysql.jdbc.Driver");
            // 2
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "123");
            // 3
            stat = conn.createStatement();
            // 4
            rs = stat.executeQuery("select * from users;");
            // 5
            while (rs.next()) {
                System.out.println(rs.getInt("id") + ".." + rs.getString("username") + ".." + rs.getString("password")
                        + ".." + rs.getDouble("salary"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6
            if(rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                rs = null;      //当对象变为null的时候, GC会优先回收该对象.
            } 

            if(stat != null) {
                try {
                    stat.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                stat = null;
            }

            if(conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    @Test
    /**
     * 增加数据
     */
    public void demo2() {
        Connection conn = null;
        Statement stat = null;

        try{
            //1
            Class.forName("com.mysql.jdbc.Driver");
            //2
            conn = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123");
            //3
            stat = conn.createStatement();
            //4
            String sql = "insert into users values(null,'隔壁老王','送快递的',5000);";
            //5
            int num = stat.executeUpdate(sql);
            if (num > 0) {
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            //6
            if(stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stat = null;
            }

            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }


    @Test 
    /**
     * 修改数据
     */
    public void demo3() {
        Connection conn = null;
        Statement stat = null;
        try {
            //1
            Class.forName("com.mysql.jdbc.Driver");
            //2
            conn = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123");
            //3
            stat = conn.createStatement();
            //4
            String sql = "update users set password='666' where id = 2;";
            int num = stat.executeUpdate(sql);
            //5
            if(num > 0) {
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6
            if(stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stat = null;
            }

            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }


    @Test 
    /**
     * 删除数据
     */
    public void demo4() {
        Connection conn = null;
        Statement stat = null;
        try {
            //1
            Class.forName("com.mysql.jdbc.Driver");
            //2
            conn = DriverManager.getConnection("jdbc:mysql:///mydb", "root", "123");
            //3
            stat = conn.createStatement();
            //4
            String sql = "delete from users where id = 2;";
            int num = stat.executeUpdate(sql);
            //5
            if(num > 0) {
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6
            if(stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stat = null;
            }

            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }
}

上面是最简单的没有任何处理的代码,当然我们在项目中是不能直接这样用代码的，现在我们使用druid来连接mysql，包括解决了sql注入问题。

1.1.2 使用Druid来连接mysql数据库

以下测试代码包含druid手动配置参数和自动设置参数后直接使用。要导入druid包~

public class dDruidTest01 {
    @Test
    public void druid(){
        Connection conn  = null;
        PreparedStatement ps  = null;
        ResultSet rs = null;
        try{
            DruidDataSource ds= new DruidDataSource();
            ds.setDriverClassName("com.mysql.jdbc.Driver");
            ds.setUrl("jdbc:mysql:///mydb");
            ds.setUsername("root");
            ds.setPassword("123");

            conn = ds.getConnection();
            String sql = "select * from count";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                System.out.println(rs.getInt("id")+"---"+rs.getString("name")+"---"+ rs.getString("salary"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    /*
     * druid带配置文件用法
     * @auther zhoujie
     */
    @Test
    public void druid2(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Properties pp = new Properties();
            pp.load(new FileInputStream("src//dgconfig.properties"));
            DataSource ds = DruidDataSourceFactory.createDataSource(pp);
            conn = ds.getConnection();
            String sql =  "update count set name = 'wangerqaaaa' where id = ?;";
//          String sql = "insert into count values(null,'lidada',20000);";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "4");
            int num = ps.executeUpdate();
            if(num>0){
                System.out.println("更新成功");
            }else{
                System.out.println("更新失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                ps.close();//自行换标准关闭代码！
                conn.close();//自行换标准关闭代码！
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }
}

druid的配置文件dgconfig.properties文件如下：

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
username=root
password=123

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

1.1.3 使用C3P0来连接mysql数据库

测试代码包含了C3P0手动和自动设置参数的案例实现

public class C3p0Demo01 {
    @Test
    public void c3p0test01(){
        Connection conn = null;
        PreparedStatement ps  = null;
        ResultSet rs = null;
        try {
            ComboPooledDataSource cpds = new ComboPooledDataSource();
            cpds.setDriverClass("com.mysql.jdbc.Driver");
            cpds.setJdbcUrl("jdbc:mysql:///mydb");
            cpds.setUser("root");
            cpds.setPassword("123");
            conn = cpds.getConnection();
            String sql = "update count set name ='woaini' where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "2");
            int num = ps.executeUpdate();
            if(num>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                ps.close();//关闭代码同上！
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }


    @Test
    public void c3p0test02(){
        Connection conn = null;
        PreparedStatement ps  = null;
        ResultSet rs = null;
        try {
            ComboPooledDataSource cpds = new ComboPooledDataSource();
            conn = cpds.getConnection();
            String sql = "update count set name ='woaini' where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "2");
            int num = ps.executeUpdate();
            if(num>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

这里c3p0的配置文件c3p0-config.xml如下：

<?xml version="1.0" encoding="UTF-8"?>
 <c3p0-config> 
     <default-config> 
         <property name="driverClass">com.mysql.jdbc.Driver</property> 
         <property name="jdbcUrl">jdbc:mysql:///mydb</property> 
         <property name="user">root</property> 
         <property name="password">123</property> 
         <property name="minPoolSize">5</property> 
         <property name="initialPoolSize">5</property> 
     </default-config>

     <named-config name="oracle"> 
         <property name="driverClass">com.mysql.jdbc.Driver</property> 
         <property name="jdbcUrl">jdbc:mysql:///day04</property> 
         <property name="user">root</property> 
         <property name="password">123</property> 
         <property name="minPoolSize">5</property> 
         <property name="initialPoolSize">5</property> 
     </named-config>
</c3p0-config>

1.1.4 使用DBUtils简化开发步骤

DBUtils工具类的使用: 执行查询语句
步骤：
A: 创建数据库连接池对象.
ComboPooledDataSource cpds = new ComboPooledDataSource();

B: 创建可以执行SQL语句的对象. QueryRunner
public QueryRunner(DataSource ds); //可以直接接受一个数据库连接池对象, 从其中直接获取连接对象.

C: 执行SQL语句, 获取结果集合.
int update(String sql, Object… params);
Xxx query(String sql,ResultSetHandler rsh ,Object… params);

        4、操作结果集。
            常见的结果集的处理方式（ResultSetHandler接口的子类）
                ArrayHandler:           //Object[]
                ArrayListHander:        //List<Object[]>
                BeanHandler:            //一个实体类的对象
                BeanListHandler:        //List<实体类的类型>
                MapHandler:             //Map<String,Object>
                MapListHandler:         //List<Map<String,Object>>
                ColumnListHandler:      //List
                ScalarHandler:          //做单值查询的
                KeyedHandler:           //Map<指定的列,Map<String,Object>>

首先定义一个JDBCUtils2工具类：

/**
* JDBCUtils工具类, 结合配置文件使用
*
* @author zhoujie
*
*/
public class JDBCUtils2 {
//私有构造方法
private JDBCUtils2() {
}

//创建一个数据库连接池对象: C3P0
private static ComboPooledDataSource cpds = new ComboPooledDataSource();


//获取数据库连接池对象的
public static DataSource getDataSource() {
    return cpds;
}


// 获取连接对象的
public static Connection getConnection() {
    try {
        return cpds.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

// 释放资源
public static void release(ResultSet rs, Statement stat, Connection conn) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        rs = null; // GC会优先回收null对象
    }
    if (stat != null) {
        try {
            stat.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        stat = null; // GC会优先回收null对象
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null; // GC会优先回收null对象
    }
}


// 释放资源
public static void release(Statement stat, Connection conn) {
    if (stat != null) {
        try {
            stat.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        stat = null; // GC会优先回收null对象
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        conn = null; // GC会优先回收null对象
    }
}

}

使用工具类，前提是配置好c3p0自动登录。


/**
 * 用DBUtils来实现 查询数据的操作
 * @author ZhouJie
 *
 */
public class Demo02 {
    // ArrayHandler: 把数据表中的第一条数据封装成 Object[]
    @Test
    public void demo1() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            Object[] obj = qr.query(sql, new ArrayHandler());
            System.out.println(Arrays.toString(obj));
            /*
             * for (Object object : obj) { System.out.println(object); }
             */
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    //ArrayListHandler: 把数据表中的每一条数据都封装成 Object[], 然后放入一个List集合中
    @Test
    public void demo2() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            List<Object[]> list = qr.query(sql, new ArrayListHandler());
            for (Object[] obj : list) {
                System.out.println(Arrays.toString(obj));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // BeanHandler: 把数据表中的第一条数据封装成 JavaBean实体类对象
    @Test
    public void demo3() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            Account acc = qr.query(sql, new BeanHandler<>(Account.class));
            System.out.println(acc);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // BeanListHandler: 把数据表中的没一条数据封装成 JavaBean实体类对象, 然后放入一个List集合中.
    @Test
    public void demo4() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            List<Account> list = qr.query(sql, new BeanListHandler<>(Account.class));
            for (Account account : list) {
                System.out.println(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // MapHandler: 把数据表中的第一条数据封装成 Map集合, 键:列名     值:该列的值
    @Test
    public void demo5() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
             Map<String, Object> map = qr.query(sql, new MapHandler());
             System.out.println(map);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // MapListHandler: 把数据表中的没一条数据封装成 Map集合, 键:列名     值:该列的值 , 然后把每一行的Map集合装入到一个List集合中.
    @Test
    public void demo6() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
            for (Map<String, Object> map : query) {
                System.out.println(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // ColunmListHandler: 把数据表中的没一条数据封装成 Map集合, 键:列名      值:该列的值 , 然后把每一行的Map集合装入到一个List集合中.
    @Test
    public void demo7() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            List<Object> list = qr.query(sql, new ColumnListHandler("money"));
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // ScalarHandler: 把数据表中的没一条数据封装成 Map集合, 键:列名      值:该列的值 , 然后把每一行的Map集合装入到一个List集合中.
    @Test
    public void demo8() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select count(*) from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            Object obj = qr.query(sql, new ScalarHandler());
            System.out.println(obj);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // KeyedHandler: 把数据表中的没一条数据封装成 Map集合, 键:列名   值:该列的值 , 然后把每一行的Map集合装入到一个List集合中.
    @Test
    public void demo9() {
        // 1. 获取可以执行SQL语句的对象: QueryRunner
        QueryRunner qr = new QueryRunner(JDBCUtils2.getDataSource());
        // 2. 编写需要执行的SQL语句
        String sql = "select * from account;";
        // 3. 执行SQL语句, 获取结果集
        try {
            Map<Object, Map<String, Object>> map = qr.query(sql, new KeyedHandler("name"));
            for (Object key : map.keySet()) {
                System.out.println(key + "..." + map.get(key));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

这里有一个Account类如下：

public class Account {
    private  int id;
    private String name;
    private String salary;
    public Account() {
        super();
        // TODO Auto-generated constructor stub
    }
    public Account(int id, String name, String salary) {
        super();
        this.id = id;
        this.name = name;
        this.salary = salary;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSalary() {
        return salary;
    }
    public void setSalary(String salary) {
        this.salary = salary;
    }
    @Override
    public String toString() {
        return "Account [id=" + id + ", name=" + name + ", salary=" + salary + "]";
    }

}

最后讲一下mysql事物处理：

public class TransactionTest {
    @Test
    public void transaction01(){
        Connection conn = null;
        PreparedStatement ps =  null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils2.getConnection();
            conn.setAutoCommit(false);
            String sql = "update count set salary = salary  + ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "1000");
            ps.setString(2, "1");
            ps.executeUpdate();

//          System.out.println(1/0);
            ps.setString(1, "-1000");
            ps.setString(2, "2");
            ps.executeUpdate();
            conn.commit();
            System.out.println("更新成功");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils2.release(rs, ps, conn);
        }
    }
}

后续再来做一个详细的总结，第一次写微博，就到这里了，没有过多的解释，哈哈 ~欢迎指正错误，交流技术。