# 命令行连接 mysql -h 主机IP -P 端口 -u 用户名 -p 密码
# 不区分大小写(默认) utf8_general_ci CREATE DATABASES xhh_01 CHARACTER SET utf8; # 区分大小写 utf8_bin CREATE DATABASES xhh_01 CHARACTER SET utf8 COLLATE utf8_bin;
备份xhh_db2
应用1:从A管理系统转移到B系统使用
应用2:防止数据库被破坏
(base) PS C:\Users\Link> mysqldump -u root -p -B xhh_db2 > E:/bak.sql Enter password: ****
mysql> source E:\\bak.sql;
mysqldump -u root -p密码 数据库 表名1 [表名2 ...] > E:/bak.sql
java.sql
javax.sql
注意版本号
-- xhh_db.actor CREATE TABLE actor( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL DEFAULT '', sex CHAR(1) NOT NULL DEFAULT '男');
package cn.xhh.jdbc_; import com.mysql.jdbc.Driver; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.9.30 */ public class Jdbc_01 { public static void main(String[] args) throws SQLException { // 1.注册驱动 // 拷贝 mysql-xxx-bin.jar 到 ./lib 点击 add to proj Driver driver = new Driver(); // 2.连接 // jdbc:mysql://192.168.137.234:3306/database_name // jdbc:mysql://localhost:3306/xhh_db2 String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; // 将用户名和密码翻入 Properties中 Properties idPassword = new Properties(); idPassword.setProperty("user", "root"); // 账号 idPassword.setProperty("password", "123456"); // 密码 Connection conn = driver.connect(url, idPassword); // 3.执行sql xhh_db2.actor // String sql1 = "INSERT INTO actor VALUES(null, 'mcy', 'F')"; String sql2 = "UPDATE actor SET name='xhh' WHERE name='mcy'"; // 用于执行静态sql语句 Statement statement = conn.createStatement(); int rows = statement.executeUpdate(sql2); // >0 :SUCCESS System.out.println(rows > 0 ? "SUCCESS!" : "FAILED."); // 4.关闭 statement.close(); conn.close(); } }
package cn.xhh.jdbc_; import com.mysql.jdbc.Driver; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.9.30 */ public class Jdbc_02 { public static void main(String[] args) throws SQLException, IllegalAccessException, InstantiationException, ClassNotFoundException, IOException { Jdbc_02 jdbc_02 = new Jdbc_02(); jdbc_02.conn05(); } // 连接方式1 public void conn01() throws SQLException { // 1.注册驱动 // 拷贝 mysql-xxx-bin.jar 到 ./lib 点击 add to proj Driver driver = new Driver(); // 2.连接 // jdbc:mysql://192.168.137.234:3306/database_name // jdbc:mysql://localhost:3306/xhh_db2 String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; // 将用户名和密码翻入 Properties中 Properties idPassword = new Properties(); idPassword.setProperty("user", "root"); // 账号 idPassword.setProperty("password", "123456"); // 密码 Connection conn = driver.connect(url, idPassword); System.out.println(conn); conn.close(); } // 连接方式2:使用反射动态加载, 灵活 public void conn02() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException { Class<?> aClass = Class.forName("com.mysql.jdbc.Driver"); Driver driver = (Driver)aClass.newInstance(); // 2.连接 String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; // 将用户名和密码翻入 Properties中 Properties idPassword = new Properties(); idPassword.setProperty("user", "root"); // 账号 idPassword.setProperty("password", "123456"); // 密码 Connection conn = driver.connect(url, idPassword); System.out.println(conn); conn.close(); } // 连接方式3: 利用DriverManager 替换 Driver 进行统一管理 public void conn03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException { Class<?> aClass = Class.forName("com.mysql.jdbc.Driver"); Driver driver = (Driver)aClass.newInstance(); String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; String user = "root"; String password = "123456"; // 注册驱动 DriverManager.registerDriver(driver); Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn); conn.close(); } // [荐]连接方式4: Class.forName 自动完成注册驱动 简化代码 public void conn04() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); // 自动注册驱动 5.1.6之后可以省去此行 String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; String user = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn); conn.close(); } // [荐]连接方式5: 把方式4 写成配置文件 public void conn05() throws IOException, SQLException, ClassNotFoundException { Properties properties = new Properties(); properties.load(new FileInputStream("./conf/mysql.properties")); String driver = properties.getProperty("driver"); String url = properties.getProperty("url"); String user = properties.getProperty("user"); String password = properties.getProperty("password"); Class.forName(driver); // 加载驱动 Connection conn = DriverManager.getConnection(url, user, password); // 连接 System.out.println(conn); conn.close(); } }
package cn.xhh.jdbc_; import java.sql.*; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.9.30 */ public class Jdbc_select { public static void main(String[] args) throws SQLException, ClassNotFoundException { // 连接 Class.forName("com.mysql.jdbc.Driver"); // 自动注册驱动 5.1.6之后可以省去此行 String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; String user = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, user, password); Statement statement = conn.createStatement(); /** * +------+------+ * | id | name | * +------+------+ * | 0 | xhh | * | 888 | mcy | * | 2 | abc | * | 3 | xyz | * +------+------+ */ // 查询语句 String sqlSelect = "SELECT id, name FROM news"; ResultSet resultSet = statement.executeQuery(sqlSelect); // 使用while 取出数据 next 把光标下移动,如果没有返回false while (resultSet.next()){ int id = resultSet.getInt(1); // 当前行 获取第1列 String name = resultSet.getString(2);// 当前行 获取第2列 System.out.println("id:" + id + " name:" + name); } // 关闭 resultSet.close(); statement.close(); conn.close(); } }
SQL注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输
入数据中注入非法的SQL语句段或命令,恶意攻击数据库。
// 输入的 账号密码 String inputName = "mcy"; String inputPassword = "3123"; String sql = "SELECT name FROM table_info WHERE name = ? AND password = ?" PreparedStatement preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1, inputName); preparedStatement.setString(2, inputPassword); // preparedStatement.executeUpdate(); // 用于 增删改 ResultSet resultSet = preparedStatement.executeQuery();// 用于查询 if(resultSet.next()){ System.out.println("登录成功"); }else { System.out.println("登录失败"); }
String sqlCRUD = "INSERT INTO admin VALUES(?, ?)"; PreparedStatement preSta = conn.prepareStatement(sqlCRUD); preSta.setString(1, "xhh"); preSta.setString(2, "0678"); int rows = preparedStatement.executeUpdate(); System.out.println(rows > 0 ? "exec Success" : "exec Failed");
package com.hspedu.jdbc.utils; import java.io.FileInputStream; import java.io.IOException; import java.sql.*; import java.util.Properties; /** * 这是一个工具类,完成 mysql的连接和关闭资源 */ public class JDBCUtils { //定义相关的属性(4个), 因为只需要一份,因此,我们做出static private static String user; //用户名 private static String password; //密码 private static String url; //url private static String driver; //驱动名 //在static代码块去初始化 static { try { Properties properties = new Properties(); properties.load(new FileInputStream("src\\mysql.properties")); //读取相关的属性值 user = properties.getProperty("user"); password = properties.getProperty("password"); url = properties.getProperty("url"); driver = properties.getProperty("driver"); } catch (IOException e) { //在实际开发中,我们可以这样处理 //1. 将编译异常转成 运行异常 //2. 调用者,可以选择捕获该异常,也可以选择默认处理该异常,比较方便. throw new RuntimeException(e); } } //连接数据库, 返回Connection public static Connection getConnection() { try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { //1. 将编译异常转成 运行异常 //2. 调用者,可以选择捕获该异常,也可以选择默认处理该异常,比较方便. throw new RuntimeException(e); } } //关闭相关资源 /* 1. ResultSet 结果集 2. Statement 或者 PreparedStatement 3. Connection 4. 如果需要关闭资源,就传入对象,否则传入 null */ public static void close(ResultSet set, Statement statement, Connection connection) { //判断是否为null try { if (set != null) { set.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { //将编译异常转成运行异常抛出 throw new RuntimeException(e); } } }
# mysql.properties # 用户名和密码 user=root password=123456 url=jdbc:mysql://192.168.137.235:3306/xhh_db driver=com.mysql.jdbc.Driver
package com.hspedu.jdbc.transaction_; import com.hspedu.jdbc.utils.JDBCUtils; import org.junit.jupiter.api.Test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; /** * @author 韩顺平 * @version 1.0 * 演示jdbc 中如何使用事务 */ public class Transaction_ { //事务来解决 public void useTransaction() { //操作转账的业务 //1. 得到连接 Connection connection = null; //2. 组织一个sql String sql = "update account set balance = balance - 100 where id = 1"; String sql2 = "update account set balance = balance + 100 where id = 2"; PreparedStatement preparedStatement = null; //3. 创建PreparedStatement 对象 try { connection = JDBCUtils.getConnection(); // 在默认情况下,connection是默认自动提交 //将 connection 设置为不自动提交 connection.setAutoCommit(false); //开启了事务 preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); // 执行第1条sql int i = 1 / 0; //抛出异常 preparedStatement = connection.prepareStatement(sql2); preparedStatement.executeUpdate(); // 执行第3条sql //这里提交事务 connection.commit(); } catch (SQLException e) { //这里我们可以进行回滚,即撤销执行的SQL //默认回滚到事务开始的状态. System.out.println("执行发生了异常,撤销执行的sql"); try { connection.rollback(); } catch (SQLException throwables) { throwables.printStackTrace(); } e.printStackTrace(); } finally { //关闭资源 JDBCUtils.close(null, preparedStatement, connection); } } }
# mysql.properties # 用户名和密码 user=root password=123456 url=jdbc:mysql://192.168.137.235:3306/xhh_db?rewriteBatchedStatements=true driver=com.mysql.jdbc.Driver
package com.hspedu.jdbc.batch_; import com.hspedu.jdbc.utils.JDBCUtils; import org.junit.jupiter.api.Test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; /** * @author 韩顺平 * @version 1.0 * 演示java的批处理 */ public class Batch_ { //使用批量方式添加数据 public void batch() throws Exception { Connection connection = JDBCUtils.getConnection(); String sql = "insert into admin2 values(null, ?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); System.out.println("开始执行"); long start = System.currentTimeMillis();//开始时间 for (int i = 0; i < 5000; i++) {//5000执行 preparedStatement.setString(1, "jack" + i); preparedStatement.setString(2, "666"); //将sql 语句加入到批处理包中 -> 看源码 /* //1. //第一就创建 ArrayList - elementData => Object[] //2. elementData => Object[] 就会存放我们预处理的sql语句 //3. 当elementData满后,就按照1.5扩容 //4. 当添加到指定的值后,就executeBatch //5. 批量处理会减少我们发送sql语句的网络开销,而且减少编译次数,因此效率提高 */ preparedStatement.addBatch(); //当有1000条记录时,在批量执行 if((i + 1) % 1000 == 0) {//满1000条sql preparedStatement.executeBatch(); //清空一把 preparedStatement.clearBatch(); } } long end = System.currentTimeMillis(); System.out.println("批量方式 耗时=" + (end - start));//批量方式 耗时=108 //关闭连接 JDBCUtils.close(null, preparedStatement, connection); } }
package cn.xhh.pool_; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.9.30 */ public class C3P0_xhh { public static void main(String[] args) throws PropertyVetoException, SQLException { // [1] 创建数据源对象(连接池) ComboPooledDataSource cpds = new ComboPooledDataSource(); String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://192.168.137.235:3306/xhh_db"; String user = "root"; String password = "123456"; // [2] 设置数据源 cpds.setDriverClass(driver); cpds.setJdbcUrl(url); cpds.setUser(user); cpds.setPassword(password); // 设置初始连接数量和最大连接数 cpds.setInitialPoolSize(6); cpds.setMaxPoolSize(20); // 获取连接 Connection conn = cpds.getConnection(); // do something ... // ..... System.out.println("Connect ... OK"); // 释放连接 (放回连接池) conn.close(); } }
c3p0-config.xml
放在 src/
<c3p0-config> <!-- 数据源名称代表连接池 --> <named-config name="xhh_mysql_conf"> <!-- 驱动类 --> <property name="driverClass">com.mysql.jdbc.Driver</property> <!-- url--> <property name="jdbcUrl">jdbc:mysql://192.168.137.235:3306/xhh_db</property> <!-- 用户名 --> <property name="user">root</property> <!-- 密码 --> <property name="password">hsp</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>
package cn.xhh.pool_; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.9.30 */ public class C3P0_xhh { public static void main(String[] args) throws SQLException { // [1] 创建数据源对象(连接池) ComboPooledDataSource cpds = new ComboPooledDataSource("xhh_mysql_conf"); // 获取连接 Connection conn = cpds.getConnection(); // do something ... // ..... System.out.println("Connect ... OK"); // 释放连接 (放回连接池) conn.close(); } }
配置文件druid.properties放在src/
#key=value driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.137.235:3306/xhh_db?rewriteBatchedStatements=true username=root password=123456 #initial connection Size 初始连接数 initialSize=10 #min idle connecton size 最小连接数 minIdle=5 #max active connection size 最大连接数 maxActive=50 #max wait time (5000 mil seconds) 最大等待事件 maxWait=5000
package cn.xhh.pool_; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.sql.Connection; import java.util.Properties; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.9.30 */ public class Druid_xhh { public static void main(String[] args) throws Exception { // 1.加入 Druid jar包 // 2.加入配置文件 // 读取配置文件 Properties properties = new Properties(); properties.load(new FileInputStream("src/druid.properties")); // 3.创建连接数数据源,指定配置文件 (连接池) DataSource ds = DruidDataSourceFactory.createDataSource(properties); Connection conn = ds.getConnection(); System.out.println("Success!"); conn.close(); } }
package com.hspedu.jdbc.datasource; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * 基于druid数据库连接池的工具类 */ public class JDBCUtilsByDruid { private static DataSource ds; //在静态代码块完成 ds初始化 static { Properties properties = new Properties(); try { properties.load(new FileInputStream("src/druid.properties")); ds = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //编写getConnection方法 public static Connection getConnection() throws SQLException { return ds.getConnection(); } //关闭连接, 老师再次强调: 在数据库连接池技术中,close 不是真的断掉连接 //而是把使用的Connection对象放回连接池 public static void close(ResultSet resultSet, Statement statement, Connection connection) { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { throw new RuntimeException(e); } } }
局部连接返回的ResultSet,在关闭的时候数据集就释放了
解决:利用ArrayList< Actor>进行存储
定义一个和数据库映射的类
把ResultSet集合中的数据放入ArrayList集合
关闭ResultSet等等资源
package com.hspedu.jdbc.datasource; import java.util.Date; /** * Actor 对象和 actor表的记录对应 * */ public class Actor { //Javabean, POJO, Domain对象 private Integer id; private String name; private String sex; private Date borndate; private String phone; public Actor() { //一定要给一个无参构造器[反射需要] } public Actor(Integer id, String name, String sex, Date borndate, String phone) { this.id = id; this.name = name; this.sex = sex; this.borndate = borndate; this.phone = phone; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBorndate() { return borndate; } public void setBorndate(Date borndate) { this.borndate = borndate; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } @Override public String toString() { return "\nActor{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", borndate=" + borndate + ", phone='" + phone + '\'' + '}'; } }
//遍历该结果集 while (set.next()) { int id = set.getInt("id"); String name = set.getString("name");//getName() String sex = set.getString("sex");//getSex() Date borndate = set.getDate("borndate"); String phone = set.getString("phone"); //把得到的resultset 的记录,封装到 Actor对象,放入到list集合 list.add(new Actor(id, name, sex, borndate, phone)); }
package com.hspedu.jdbc.datasource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.junit.jupiter.api.Test; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * @author 韩顺平 * @version 1.0 */ @SuppressWarnings({"all"}) public class DBUtils_USE { //使用apache-DBUtils 工具类 + druid 完成对表的crud操作 @Test public void testQueryMany() throws SQLException { //返回结果是多行的情况 //1. 得到 连接 (druid) Connection connection = JDBCUtilsByDruid.getConnection(); //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project //3. 创建 QueryRunner QueryRunner queryRunner = new QueryRunner(); //4. 就可以执行相关的方法,返回ArrayList 结果集 //String sql = "select * from actor where id >= ?"; // 注意: sql 语句也可以查询部分列 String sql = "select id, name from actor where id >= ?"; // 老韩解读 //(1) query 方法就是执行sql 语句,得到resultset ---封装到 --> ArrayList 集合中 //(2) 返回集合 //(3) connection: 连接 //(4) sql : 执行的sql语句 //(5) new BeanListHandler<>(Actor.class): 在将resultset -> Actor 对象 -> 封装到 ArrayList // 底层使用反射机制 去获取Actor 类的属性,然后进行封装 //(6) 1 就是给 sql 语句中的? 赋值,可以有多个值,因为是可变参数Object... params //(7) 底层得到的resultset ,会在query 关闭, 关闭PreparedStatment List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1); System.out.println("输出集合的信息"); for (Actor actor : list) { System.out.print(actor); } //释放资源 JDBCUtilsByDruid.close(null, null, connection); } //演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象) @Test public void testQuerySingle() throws SQLException { //1. 得到 连接 (druid) Connection connection = JDBCUtilsByDruid.getConnection(); //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project //3. 创建 QueryRunner QueryRunner queryRunner = new QueryRunner(); //4. 就可以执行相关的方法,返回单个对象 String sql = "select * from actor where id = ?"; // 老韩解读 // 因为我们返回的单行记录<--->单个对象 , 使用的Hander 是 BeanHandler Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 10); System.out.println(actor); // 释放资源 JDBCUtilsByDruid.close(null, null, connection); } //演示apache-dbutils + druid 完成查询结果是单行单列-返回的就是object @Test public void testScalar() throws SQLException { //1. 得到 连接 (druid) Connection connection = JDBCUtilsByDruid.getConnection(); //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project //3. 创建 QueryRunner QueryRunner queryRunner = new QueryRunner(); //4. 就可以执行相关的方法,返回单行单列 , 返回的就是Object String sql = "select name from actor where id = ?"; //老师解读: 因为返回的是一个对象, 使用的handler 就是 ScalarHandler Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 4); System.out.println(obj); // 释放资源 JDBCUtilsByDruid.close(null, null, connection); } //演示apache-dbutils + druid 完成 dml (update, insert ,delete) @Test public void testDML() throws SQLException { //1. 得到 连接 (druid) Connection connection = JDBCUtilsByDruid.getConnection(); //2. 使用 DBUtils 类和接口 , 先引入DBUtils 相关的jar , 加入到本Project //3. 创建 QueryRunner QueryRunner queryRunner = new QueryRunner(); //4. 这里组织sql 完成 update, insert delete //String sql = "update actor set name = ? where id = ?"; //String sql = "insert into actor values(null, ?, ?, ?, ?)"; String sql = "delete from actor where id = ?"; //老韩解读 //(1) 执行dml 操作是 queryRunner.update() //(2) 返回的值是受影响的行数 (affected: 受影响) //int affectedRow = queryRunner.update(connection, sql, "林青霞", "女", "1966-10-10", "116"); int affectedRow = queryRunner.update(connection, sql, 1000 ); System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响到表"); // 释放资源 JDBCUtilsByDruid.close(null, null, connection); } }
data access object
#key=value driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.137.235:3306/xhh_db?rewriteBatchedStatements=true username=root password=123456 #initial connection Size 初始连接数 initialSize=10 #min idle connecton size 最小连接数 minIdle=5 #max active connection size 最大连接数 maxActive=50 #max wait time (5000 mil seconds) 最大等待事件 maxWait=5000
package cn.xhh.daotest.utils; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.10.2 */ public class JDBCUtilsByDruid { private static DataSource ds; //在静态代码块完成 ds初始化 static { Properties properties = new Properties(); try { properties.load(new FileInputStream("src/druid.properties")); ds = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //编写getConnection方法 public static Connection getConnection() throws SQLException { return ds.getConnection(); } //关闭连接, 老师再次强调: 在数据库连接池技术中,close 不是真的断掉连接 //而是把使用的Connection对象放回连接池 public static void close(ResultSet resultSet, Statement statement, Connection connection) { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { throw new RuntimeException(e); } } }
package cn.xhh.daotest.dao; import cn.xhh.daotest.utils.JDBCUtilsByDruid; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import java.sql.Connection; import java.sql.SQLException; import java.util.List; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.10.2 */ public class BasicDAO<T> { private QueryRunner qr = new QueryRunner(); // 开发通用的dml public int update(String sql, Object... parameters){ Connection conn = null; try { conn = JDBCUtilsByDruid.getConnection(); return qr.update(conn, sql, parameters); } catch (SQLException e) { // e.printStackTrace(); throw new RuntimeException(e); // }finally { JDBCUtilsByDruid.close(null, null, conn); } } // 完成返回多个对象 针对任意的表 /** * * @param sql * @param classT Actor.class * @param parameters 传入? * @return */ // 查询多行 public List<T> queryMulti(String sql, Class<T> classT, Object... parameters){ Connection conn = null; try { conn = JDBCUtilsByDruid.getConnection(); return qr.query(conn, sql, new BeanListHandler<T>(classT), parameters); } catch (SQLException e) { throw new RuntimeException(e); }finally { JDBCUtilsByDruid.close(null, null, conn); } } // 查询单行 public T queryLine(String sql, Class<T> classT, Object... parameters){ Connection conn = null; try { conn = JDBCUtilsByDruid.getConnection(); return qr.query(conn, sql, new BeanHandler<>(classT), parameters); } catch (SQLException e) { throw new RuntimeException(e); }finally { JDBCUtilsByDruid.close(null, null, conn); } } // 查询Scalar public Object queryScalar(String sql, Object... parameters){ Connection conn = null; try { conn = JDBCUtilsByDruid.getConnection(); return qr.query(conn, sql, new ScalarHandler(), parameters); } catch (SQLException e) { throw new RuntimeException(e); }finally { JDBCUtilsByDruid.close(null, null, conn); } } }
package cn.xhh.daotest.dao; import cn.xhh.daotest.domain.Actor; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.10.2 */ public class ActorDAO extends BasicDAO<Actor>{ // 1.有公用方法 // 2.可以添加个性化方法 }
package cn.xhh.daotest.domain; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.10.2 */ public class Actor { // need public Actor() { } public Actor(Integer id, String name, String sex) { this.id = id; this.name = name; this.sex = sex; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } @Override public String toString() { return "Actor{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + '}'; } private Integer id; private String name; private String sex; }
package cn.xhh.daotest.test; import cn.xhh.daotest.dao.ActorDAO; import cn.xhh.daotest.domain.Actor; import java.util.List; /** * @author : xhh * @email : xhh0608@foxmail.com * @date : 2021.10.2 */ public class TestActorDao { public static void main(String[] args) { ActorDAO actorDAO = new ActorDAO(); // 测试查询 String sql = "SELECT * FROM actor WHERE sex=? AND name=?"; List<Actor> actors = actorDAO.queryMulti(sql, Actor.class, "G", "mcy"); for (Actor o :actors) { System.out.println(o); } // // 测试插入 // sql = "INSERT INTO actor VALUES(?, ?, ?)"; // int update = actorDAO.update(sql, null, "abc", "G"); // System.out.println(update > 0 ? "Insert Success" : "Insert Failed"); // 查询Line sql = "SELECT * FROM actor WHERE id=?"; Actor actor = actorDAO.queryLine(sql, Actor.class, 1); System.out.println(actor); // 查询姓名 sql = "SELECT ac.name FROM actor AS ac WHERE id=?"; Object o = actorDAO.queryScalar(sql, 1); System.out.println(o); } }
参考
[1] 韩顺平-零基础30天学会Java