JDBC(MySQL)

JDBC(MySQL)

​ 使用JDBCUtils的优点，在我们有大量使用mysql的数据库的情况下，我们可以通过更改jdbc.properties配置文件就可以修改数据库的配置，而不是寻找代码然后在一次次更改代码中的数据

properties文件，文件名user.properties

user=pxx
password=123
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/pxx

//通过propertices获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JavaTest/day2/user.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");

        //注册驱动，建议写上兼容之前的版本
        Class.forName(driver);
        //得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //Mysql语句
        String sql ="insert into students values(?,?,?)";
        //preparedStatement对象实现PreparedStatement接口的实现类的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给？赋值
        preparedStatement.setInt(1,102192121);
        preparedStatement.setString(2, "彭大旧");
        preparedStatement.setString(3,"男");
        //执行sql语句executeUpdate(),返回影响行数
        // 执行查询语句executeQuery(),返回ResultSet对象，要用while循环，next是指向结果下一行
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt(1);  //第一列
            String name = resultSet.getString(2);
            String gender = resultSet.getString(3);
            System.out.println(id + "\t" + name + "\t" +gender);
        }
        // execute()执行任意sql语句返回布尔值

        int row = preparedStatement.executeUpdate();
        System.out.println(row>0?"successful":"fault");
        //关连接
        preparedStatement.close();
        connection.close();

JDBCUtils

public class JDBCUtils {

   private static String url;
   private static String user;
   private static String password;
   private static String driver;

   static {

       try {
           //1. 创建Properties集合类。
           Properties properties = new Properties();

           //获取src路径下的文件的方式--->ClassLoader 类加载器
           ClassLoader classLoader = JDBCUtils.class.getClassLoader();
           URL res  = classLoader.getResource("jdbc.properties");
           String path = res.getPath();
           //2. 加载文件
           properties.load(new FileReader(path));

           url = properties.getProperty("url");
           user = properties.getProperty("user");
           password = properties.getProperty("password");
           driver = properties.getProperty("driver");

           //注册驱动
           Class.forName(driver);


       } catch (IOException e) {
           e.printStackTrace();
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }


   }


   /**
    * 获取连接
    * @return 连接对象
    */
   public static Connection getConnection() throws SQLException {

       return DriverManager.getConnection(url, user, password);
   }


   /**
    * 释放资源
    * @param statement
    * @param connection
    */

   public static void close(Statement statement, Connection connection) {
       if (statement != null) {
           try {
               statement.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }
       if (connection != null) {
           try {
               connection.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }

   }

   /**
    * 释放资源
    * @param resultSet
    * @param statement
    * @param connection
    */
   public static void close(Statement statement, Connection connection,ResultSet resultSet) {
       if (resultSet != null) {
           try {
               resultSet.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }

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

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

   }

}



/**
* JDBCUtils的测试类
* 
*/
public class JDBCUtilsDemo {

   public static void main(String[] args) {

       List<student> students = new JDBCUtilsDemo().finAll();
       System.out.println(students);
       System.out.println(students.size());

   }

   public List<student> finAll() {

       Connection connection = null;
       PrepareStatement prepareStatement = null;
       ResultSet resultSet = null;
       String sql = "SELECT * from student where id = ?";

       try {

           //1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
           //2. 注册驱动
           //以上2步骤都通过JDBCUtils来简化了
           connection=JDBCUtils.getConnection();
           //3.获取执行sql的对象
           prepareStatement = connection.prepareStatement(sql);
           prepareStatement.setInt(1,102)
           //4.执行sql
           resultSet = prepareStatement.executeQuery();
           //5.遍历结果集，封装对象，装载集合
           student stu = null;
           list = new ArrayList<student>();
           while (resultSet.next()) {
               int id = resultSet.getInt("id");
               String ename = resultSet.getString("name");
               System.out.println(id + "\t" + name + "\t");
           }

       } catch (SQLException throwables) {
           throwables.printStackTrace();
       } finally {
           JDBCUtils.close(statement,connection,resultSet);
       }
       
   }
}

事务提交回滚

Connection connection = null;
//Mysql语句
String sql ="update students set name = '彭大新' where id = 102192122";
String sql1 ="update students set name = '彭大大' where id = 102192121";
PreparedStatement preparedStatement = null;
//得到连接
try {
    connection = JDBCUtils.getConnection();
    connection.setAutoCommit(false);
    //preparedStatement对象实现PreparedStatement接口的实现类的对象
    preparedStatement = connection.prepareStatement(sql);
    //执行sql语句executeUpdate(),返回影响行数
    preparedStatement.executeUpdate();
    int i = 1/0; //抛出异常
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.executeUpdate();
    connection.commit();
} catch (SQLException e) {
    try {
        connection.rollback();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}finally {
    JDBCUtils.close(preparedStatement,connection,null);
}

JDBC批量处理

//properties文件
//url=jdbc:mysql://localhost:3306/pxx?rewriteBatchedStatements=true
Connection connection = JDBCUtils.getConnection();
String sql = "insert into students values(?,?,'男')";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
long start =System.currentTimeMillis();
for (int i = 0;i<5000;i++){
    preparedStatement.setInt(1,i);
    preparedStatement.setString(2,"彭彭彭"+ i);
    preparedStatement.addBatch();
    if (i + 1 %1000 == 0){
        preparedStatement.executeUpdate();
        preparedStatement.clearBatch();
    }

}
long end = System.currentTimeMillis();
System.out.println(end - start);

C3P0（hibernate，spring底层）

需要引进c3p0 jar包

<c3p0-config>

  <named-config name="pxx">
<!-- 驱动类 -->
  <property name="driverClass">com.mysql.jdbc.Driver</property>
  <!-- url-->
  	<property name="jdbcUrl">jdbc:mysql://localhost:3306/pxx</property>
  <!-- 用户名 -->
  		<property name="user">pxx</property>
  		<!-- 密码 -->
  	<property name="password">123</property>
  	<!-- 每次增长的连接数-->
    <property name="acquireIncrement">5</property>
    <!-- 初始的连接数 -->
    <property name="initialPoolSize">10</property>
    <!-- 最小连接数 -->
    <property name="minPoolSize">5</property>
   <!-- 最大连接数 -->
    <property name="maxPoolSize">50</property>

	<!-- 可连接的最多的命令对象数 -->
    <property name="maxStatements">5</property> 
    
    <!-- 每个连接对象可连接的最多的命令对象数 -->
    <property name="maxStatementsPerConnection">2</property>
  </named-config>
</c3p0-config>

//使用配置文件模板完成
//将C3P0提供的c3p0.config.xml拷贝到src目录下
//该文件指定了连接数据库和连接池的相关参数
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("pxx");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
    Connection connection = comboPooledDataSource.getConnection();
    connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);

Druid(德鲁伊)

需要引进jar包

//加入jar包，及配置文件druid.properties
//创建Properties对象，读取配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
    Connection connection = dataSource.getConnection();
    connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);

JDBCUtilsByDruid

 public class JDBCUtilsByDruid {
        private static DataSource dataSource;
        static {
            Properties properties = new Properties();
            try {
                properties.load(new FileInputStream("src\\druid.properties"));
                dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        public static Connection getConnection() throws SQLException {
            return dataSource.getConnection();
        }

        public static void close (Statement statement, Connection connection, ResultSet resultSet){
            try {
                if (resultSet != null){
                    resultSet.close();
                }
                if (statement != null){
                    statement.close();
                }
                if (connection != null){
                    connection.close();
                }
            }catch (SQLException e){
                throw new RuntimeException(e);
            }

        }
    public ArrayList<Students> test() {
        //得到连接
        Connection connection = null;
        //sql语句
        String sql = "select * from students where id>=?";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        ArrayList<Students> list = new ArrayList<>();
        //创建PreparedStatement对象
        try {
            connection = JDBCUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,0);
            //执行得到结果集
            set = preparedStatement.executeQuery();
            //遍历结果集
            while (set.next()){
                int id = set.getInt("id");
                String name = set.getString("name");
                String gender = set.getString("gender");
                list.add(new Students(id,name,gender));
            }
            System.out.println("list集合"+list);
            for (Students students : list){
                System.out.println("id=" + students.getId() + "\t" + students.getName());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsByDruid.close(preparedStatement,connection,set);
        }
        return list;
    }
}

Apache—DBUtils

开源JDBC工具类库，是对JDBC的封装，使用Dbutils类能极大简化JDBC编码的工作量

QueryRunner类，该类封装了SQL的执行，是线程安全的，可以实现增、删、改、查、批处理

ResultSetHandlder接口，该接口用于处理java.sql.ResultSet，将数据按要求转换成另一种形式

 //得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //使用DBUtils类和接口，先引入DBUtils相关的jar，加入到本Project
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //执行相关的方法，返回ArrayList结果集
        String sql = "select * from students where id>=?";
        //(1)query方法就是执行sql语句，得到resultset----封装到----ArrayList集合中
        //(2)返回集合
        //(3)connection:连接
        //(4)sql:执行的sql语句
        //(5)new BeanListHandler<>(Students.class):在将resultset---Students---封装到ArrayList
        //底层使用反射机制去获取Students类的属性，然后进行封装
        //(6)0 是给sql语句中的？赋值，可以有多个值，因为是可变参数Object...params
        //(7)底层得到的resultset，会在query关闭，关闭PrepareStatment
		//需要注意，Students类需要有无参构造器，底层通过反射，调用newInstance()方法，该方法只能调用无参构造
        List<Students> list = queryRunner.query(connection, sql, new BeanListHandler<>(Students.class), 0);
        for (Students students : list){
            System.out.println(students);
        }
        JDBCUtilsByDruid.close(null,connection,null);