JDBC学习

JDBC

数据库驱动

驱动：声卡、显卡、数据库

程序会通过数据库驱动和数据库打交道

JDBC

sun公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java数据库的）规范，俗称JDBC，这些规范的实现由具体的厂商去做

开发人员只需要掌握JDBC接口的操作即可

java.sql

javax.sql

(数据库驱动包) mysql-connector

第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);

INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,‘zhangsan’,‘123456’,‘zs@sina.com’,1980-12-04),
(2,‘lisi’,‘123456’,‘lisi@sina.com’,1981-12-04),
(3,‘wangwu’,‘123456’,‘wangwu@sina.com’,1979-12-04);

创建一个项目

导入数据库驱动

编写测试代码

package com.liu.test01;

import java.sql.*;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 14:35
 */


//第一个JDBC程序
public class jdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");  //固定写法

        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String psd = "123456";
        //3.连接成功，返回一个数据库对象    connection  代表数据
        Connection connection = DriverManager.getConnection(url, username, psd);

        //4.创建执行sql的对象  statement  执行sql的对象

        Statement statement = connection.createStatement();
        //5.执行sql
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);   //返回的结果集，封装了查询的结果集

        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("psd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("========================================================");
        }

        //6.释放连接

        resultSet.close();
        statement.close();
        connection.close();
    }

}

result

步骤总结

1、加载驱动

2、连接数据库 DriverManager

3、获得执行sql的对象 Statement

4、获得返回的结果集

5、释放连接

DriverManager

//  老版本的写法    DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver");  //固定写法

URl

String url "jdbc:mysql://localhost:3306/jdbcstudy?
    useUnicode=true&characterEncoding=true&useSSL=true
//mysql默认端口号  3306
//jdbc：mysql://主机地址：端口号/数据库名/表明？参数1&参数2.......


    //useUnicode  
//characterEncoding   字符编码
    //useSSL  使用安全的线路

connection 代表数据库

connection.commit();
connection.setAutoCommit();
connection.rollback();

Statement 执行Sql语句的对象 PrepareStatement

statement.executeQuery();   //执行查询SQL
statement.execute;  //执行任何SQL
statement.excuteUpdate();   //执行更新SQL(更新、插入、删除)

Re sultSet 查询的结果集，封装了所有的查询结果

resultSet.getObject();
resultSet.getInt();
resultSet.getString();
resultSet.getFloat();
resultSet.getDate();
........
    
    //遍历
resultset.beforeFirst();//移动到最前面
resu1tset.afterLast();//移动到最后面
resu1tset.next();//移动到下一个数据
resu1tset.previous();//移动到前一行
resu1tset.absolute(row);//移动到指定行row

释放资源

        resultSet.close();
        statement.close();
        connection.close();

JDBC工具类

package com.liu.test01.utiles;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 15:33
 */
public class jdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = jdbcUtils.class.getResourceAsStream("db.properties");
            Properties propertise = new Properties();
            propertise.load(in);
            driver = propertise.getProperty("driver");
            url = propertise.getProperty("url");
            username = propertise.getProperty("username");
            password = propertise.getProperty("password");


            //驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取链接

    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url,username,password);

    }


    //释放资源

    public static void release(Connection con, Statement sta, ResultSet RS) throws SQLException {
        if (RS != null) {
            RS.close();
        }
        if (sta != null) {
            sta.close();
        }
        if (con != null) {
            con.close();
        }
    }

}

增

package com.liu.test01.lesson;
import com.liu.test01.utiles.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 15:48
 */
public class sqlInsert {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st =null;
        ResultSet rs = null;
        try {
            con = jdbcUtils.getConnection();  //获取数据库连接
            st = con.createStatement();
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'胡亚莉','666666','sadsadada@qq.com','2009-6-9')";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("执行成功");
            }
        } catch (Exception e) {
           e.printStackTrace();
        }finally {
            jdbcUtils.release(con,st,rs);
        }
    }
}

删

package com.liu.test01.lesson;
import com.liu.test01.utiles.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 15:48
 */
public class sqldelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st =null;
        ResultSet rs = null;
        try {
            con = jdbcUtils.getConnection();  //获取数据库连接
            st = con.createStatement();
            String sql = "DELETE FROM users WHERE ID = 4";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("执行成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(con,st,rs);
        }
    }
}

改

package com.liu.test01.lesson;
import com.liu.test01.utiles.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 15:48
 */
public class sqlUpdate {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st =null;
        ResultSet rs = null;
        try {
            con = jdbcUtils.getConnection();  //获取数据库连接
            st = con.createStatement();
            String sql = "UPDATE users SET email = '3303473441@qq.com' WHERE id = 5 ";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("执行成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(con,st,rs);
        }
    }
}

查询

package com.liu.test01.lesson;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.liu.test01.utiles.jdbcUtils;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 16:54
 */
public class testSelect {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = jdbcUtils.getConnection();
            st = con.createStatement();
            String sql = "SELECT * from users where id =5";
            rs = st.executeQuery(sql);  //将得到的结果集放入rs
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("email"));
                System.out.println(rs.getInt("id"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(con,st,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
} 

sql注入

package com.liu.test01.lesson;

import com.liu.test01.utiles.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 17:03
 */
public class sql注入 {
    public static void main(String[] args) {
            login("' or'1=1","' or ' 5>0");
    }

    public static void login(String name,String psd){
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = jdbcUtils.getConnection();
            st = con.createStatement();
            String sql = "SELECT * from users where `NAME`='"+name+"' AND `PASSWORD` ='"+psd+"'";
            rs = st.executeQuery(sql);  //将得到的结果集放入rs
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("email"));
                System.out.println(rs.getInt("id"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(con,st,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    }

执行结果

PrepareStatement 可以防止sql注入 并且效率更高

package com.liu.test01.Transaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.liu.test01.utiles.jdbcUtils;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 23:56
 */
public class Demo01 {
    public static void main(String[] args) {
        Connection con= null;
        PreparedStatement pst =  null;
        ResultSet rs = null;

        try {
            con = jdbcUtils.getConnection();
            //关闭自动提交后，会自动开始事务
            con.setAutoCommit(false);

            String sql1 = "update account set money = money-500 where NAME = 'A'";

            pst = con.prepareStatement(sql1);
            int i1 = pst.executeUpdate();
            if (i1!=0){
                System.out.println("1成功");
            }
            String sql2 = "update account set money = money+500 where NAME = 'B'";

            pst = con.prepareStatement(sql2);
            int i2 = pst.executeUpdate();
            if (i2!=0){
                System.out.println("2成功");
            }
            con.commit();
            System.out.println("转账成功");

        } catch (SQLException throwables) {
            try {
                con.rollback();  //如果失败就回滚事务
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(con,pst,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
}

package com.liu.test01.Transaction;

import com.liu.test01.utiles.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @version 1.0
 * @description:TODO
 * @Author:
 * @date :2021/10/5 23:56
 */
public class Demo02 {
    public static void main(String[] args) {
        Connection con= null;
        PreparedStatement pst =  null;
        ResultSet rs = null;

        try {
            con = jdbcUtils.getConnection();
            //关闭自动提交后，会自动开始事务
            con.setAutoCommit(false);

            String sql1 = "update account set money = money-500 where NAME = 'A'";
            pst = con.prepareStatement(sql1);
            int i1 = pst.executeUpdate();
            if (i1!=0){
                System.out.println("1成功");
            }
            int x = 1/0;
            String sql2 = "update account set money = money+500 where NAME = 'B'";
            pst = con.prepareStatement(sql2);
            int i2 = pst.executeUpdate();
            if (i2!=0){
                System.out.println("2成功");
            }
            con.commit();
            System.out.println("转账成功");

        } catch (SQLException throwables) {
            try {
                con.rollback();  //如果失败就回滚事务,如果失败默认回滚
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            try {
                jdbcUtils.release(con,pst,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
}

PrepareStatement 可以防止注入

数据库连接池

数据库连接—执行语句—执行完毕—释放

连接—>释放 十分浪费系统资源

池化技术：准备预先的资源，使用时就直接到池子里使用预先准备好的connection

开源数据实现

DBCP

package com.liu.test01.DBCP;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import com.liu.test01.utiles.jdbcUtils;
import javax.sql.DataSource;

/**
* @version 1.0
* @description:TODO
* @Author:
* @date :2021/10/5 15:33
*/
public class DBCPUtils {
/*
   private static String driver = null;
   private static String url = null;
   private static String username = null;
   private static String password = null;
 */   //使用连接池后，就不需要配置连接数据库的代码了


   private static DataSource dataSource = null;

   static {
       try {
           InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("DBCPprofit.properties");
           Properties properties = new Properties();
           properties.load(in);


           //创建数据源  工厂模式(创建对象)
           dataSource = BasicDataSourceFactory.createDataSource(properties);
           //驱动只用加载一次
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

   //获取链接

   public static Connection getConnection() throws SQLException {

       return dataSource.getConnection();
   }


   //释放资源

   public static void release(Connection con, Statement sta, ResultSet RS) throws SQLException {
       if (RS != null) {
           RS.close();
       }
       if (sta != null) {
           sta.close();
       }
       if (con != null) {
           con.close();
       }
   }

}

package com.liu.test01.DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.liu.test01.utiles.jdbcUtils;
import com.liu.test01.DBCP.DBCPUtils;
/**
* @version 1.0
* @description:TODO
* @Author:
* @date :2021/10/5 23:56
*/
public class testDBCPUtils {
   public static void main(String[] args) {
       Connection con= null;
       PreparedStatement pst =  null;
       ResultSet rs = null;
       try {
           con = DBCPUtils.getConnection();
           String sql = "insert into users()values(?,?,?,?,?);";

           pst = con.prepareStatement(sql);

           pst.setInt(1,9);
           pst.setString(2,"陈龙");
           pst.setString(3,"1234567889");
           pst.setString(4,"7879841@qq.com");
           pst.setDate(5,new java.sql.Date(new Date().getTime()));
           int i = pst.executeUpdate();
           if (i!=0){
               System.out.println("执行成功！ ");
           }
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }finally {
           try {
               DBCPUtils.release(con,pst,rs);
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }


   }
}

C3P0

package com.liu.test01.C3P0;

import com.liu.test01.utiles.jdbcUtils;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @version 1.0
* @description:TODO
* @Author:
* @date :2021/10/5 15:33
*/
public class C3P0Utils {
   private static DataSource datasource = null;
   static {
       try {
           //创建数据源  工厂模式(创建对象)  C3P0不需要导入xml文件，程序会根据给的“MySQL“ 自动去匹配
           datasource = new ComboPooledDataSource("MySQL");
           //驱动只用加载一次
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

   //获取链接

   public static Connection getConnection() throws SQLException {

       return datasource.getConnection();
   }


   //释放资源

   public static void release(Connection con, Statement sta, ResultSet RS) throws SQLException {
       if (RS != null) {
           RS.close();
       }
       if (sta != null) {
           sta.close();
       }
       if (con != null) {
           con.close();
       }
   }

}

package com.liu.test01.C3P0;

import com.liu.test01.DBCP.DBCPUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
* @version 1.0
* @description:TODO
* @Author:
* @date :2021/10/5 23:56
*/
public class testC3P2Utils {
   public static void main(String[] args) {
       Connection con= null;
       PreparedStatement pst =  null;
       ResultSet rs = null;
       try {
           con = C3P0Utils.getConnection();
           String sql = "insert into users()values(?,?,?,?,?);";

           pst = con.prepareStatement(sql);

           pst.setInt(1,8);
           pst.setString(2,"李鸿");
           pst.setString(3,"123456788");
           pst.setString(4,"99999999@qq.com");
           pst.setDate(5,new java.sql.Date(new Date().getTime()));
           int i = pst.executeUpdate();
           if (i!=0){
               System.out.println("执行成功！ ");
           }
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }finally {
           try {
               DBCPUtils.release(con,pst,rs);
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }


   }
}

Druid：阿里

使用这些数据连接池后，在开发项目中就不需要编写连接数据库的代码了,提高工作效率

      String sql = "insert into users()values(?,?,?,?,?);";

       pst = con.prepareStatement(sql);

       pst.setInt(1,8);
       pst.setString(2,"李鸿");
       pst.setString(3,"123456788");
       pst.setString(4,"99999999@qq.com");
       pst.setDate(5,new java.sql.Date(new Date().getTime()));
       int i = pst.executeUpdate();
       if (i!=0){
           System.out.println("执行成功！ ");
       }
   } catch (SQLException throwables) {
       throwables.printStackTrace();
   }finally {
       try {
           DBCPUtils.release(con,pst,rs);
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
   }

}
}

Druid：阿里

使用这些数据连接池后，在开发项目中就不需要编写连接数据库的代码了,提高工作效率