预处理语句完成CRUD
1、 插入
// 插入public static void insert() throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2311?useSSL=false", "root", "123456");
// 1 sqlString sql = "insert into tb_user values (?,?,?,?,?,?,?)";// 2 预处理PreparedStatement ps = conn.prepareStatement(sql);// 3 给?赋值ps.setInt(1,6);ps.setString(2,"JK");ps.setString(3,"123456");ps.setString(4,"5555");/*** setDate() 需要传入java.sql.Date包下的日期对象* java.sql.Date没有无参构造,只有一个有参构造,传入是毫秒值*/// 方案1: 获得当前时间毫秒值// java.util.Date date = new java.util.Date( );// 创建当前日期对象// long time = date.getTime( );// 获得当前毫秒值// java.sql.Date date2 = new java.sql.Date(time);// 方案2: 获得指定时间的毫秒值LocalDate localDate = LocalDate.of(2020, 10, 1);java.util.Date date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());long time = date.getTime();// java.sql.Date date2 = new java.sql.Date(time);// 方案3: 获得系统毫秒值//long time = System.currentTimeMillis( );// 获得当前毫秒值
ps.setDate(5,new java.sql.Date(time));ps.setDouble(6,4800);ps.setInt(7,1);
// 4 执行int i = ps.executeUpdate( );System.out.println(i > 0 ?"OK":"ERR" );
ps.close();conn.close();}
10.2 更新
public static void main(String[] args) throws Exception {LocalDate localDate = LocalDate.of(1999, 10, 1);java.util.Date date = java.util.Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());User user = new User(1,"mlxg","6666","1111",date,6300,1);update(user);}
// 更新public static void update(User user) throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2311?useSSL=false", "root", "123456");
// 1 sqlString sql = "update tb_user set username = ?,password = ? ," +"phone = ?,create_time = ?,money = ?,sex = ? " +"where id = ?";// 2 预处理PreparedStatement ps = conn.prepareStatement(sql);// 3 给?赋值ps.setString(1,user.getUsername());ps.setString(2,user.getPassword());ps.setString(3,user.getPhone());ps.setDate(4,new java.sql.Date(user.getCreateTime().getTime()));ps.setDouble(5,user.getMoney());ps.setInt(6,user.getSex());ps.setInt(7,user.getId());
// 4 执行int i = ps.executeUpdate( );System.out.println(i > 0 ?"OK":"ERR" );
ps.close();conn.close();}
10.3 删除
// 删除public static void delete() throws Exception {Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2311?useSSL=false", "root", "123456");
// 1 先写sqlString sql = "delete from tb_user where id = ?";// 2 预处理PreparedStatement ps = conn.prepareStatement(sql);// 3 ?赋值ps.setInt(1,4);// 4 执行int i = ps.executeUpdate( );System.out.println(i > 0 ?"OK":"ERR" );
ps.close();conn.close();}
10.4 查询
// 根据id从数据库查询出用户信息public static User getUserById(int id) throws Exception {// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);User user = null;// 3 预处理// 3.1 写sql,参数用?占位String sql = "select * from tb_user where id = ?";// 3.2 预处理?号PreparedStatement ps = conn.prepareStatement(sql);// 3.3 给?赋值ps.setInt(1,id);
// 4 执行sqlResultSet rs = ps.executeQuery( );
if(rs.next()) {// 取出数据String username1 = rs.getString("username");String password1 = rs.getString("password");String phone = rs.getString("phone");Date createTime = rs.getDate("createTime");double money = rs.getDouble("money");int sex = rs.getInt("sex");
// 封装数据user = new User();user.setId(id);user.setUsername(username1);user.setPassword(password1);user.setPhone(phone);user.setCreateTime(createTime);user.setMoney(money);user.setSex(sex);}
return user;}// 设计方法,查询全部数据,返回值是List集合,集合中是全部用户数据public static List<User> findAll() throws Exception{// 1 注册驱动Class.forName("com.mysql.Driver");// 2 获得连接String url = "jdbc:mysql://localhost:3306/java2301?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false";String username = "root";String password = "123456";Connection conn = DriverManager.getConnection(url, username, password);ArrayList<User> list = new ArrayList<>( );User user = null;// 3.1 写sqlString sql = "select * from tb_user";// 3.2 处理sqlPreparedStatement ps = conn.prepareStatement(sql);
// 4 执行ResultSet rs = ps.executeQuery( );
while(rs.next()) {// 取出数据int id = rs.getInt("id");String username1 = rs.getString("username");String password1 = rs.getString("password");String phone = rs.getString("phone");Date createTime = rs.getDate("createTime");double money = rs.getDouble("money");int sex = rs.getInt("sex");
// 封装数据user = new User();user.setId(id);user.setUsername(username1);user.setPassword(password1);user.setPhone(phone);user.setCreateTime(createTime);user.setMoney(money);user.setSex(sex);
// 【重点】 将对象存储入集合list.add(user);}
return list;}
十一、DbUtils【重点,理解,会用】
11.1 介绍
Apache DBUtils是一个流行的数据库工具库,它提供了一组简单、轻量级的工具和接口,用于简化与关系型数据库进行交互的过程。
ResultSetHandler接口:转换类型接口
BeanHandler类:实现类,把一条记录转换成对象
BeanListHandler类:实现类,把多条记录转换成List集合。
ScalarHandler类:实现类,适合获取一行一列的数据。
QueryRunner:执行sql语句的类
增、删、改:
update()
;查询:
query()
;
11.2 依赖
<dependency><groupId>commons-dbutils</groupId><artifactId>commons-dbutils</artifactId><version>1.7</version></dependency>
11.3 使用
public class Demo6 {public static void main(String[] args) throws Exception {// delete( );// insert();// selectOne();selectAll();}private static void delete() throws SQLException {// 加载驱动不再需要// 通过驱动管理器获得连接Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2313?useSSL=false", "root", "123456");// 创建sql执行器QueryRunner queryRunner = new QueryRunner( );/*** 参数1: 数据库连接对象* 参数2: sql语句字符串,也需要参数变?* 参数3: 给?赋值*/int i = queryRunner.update(conn, "delete from tb_user where id = ?", 6);System.out.println(i );DbUtils.closeQuietly(conn);}private static void insert() throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2311?useSSL=false", "root", "123456");QueryRunner queryRunner = new QueryRunner( );Object[] params = {6,"zzz","123456","1111",new java.sql.Date(1),1000.0,1};/*** update()方法的第三个参数是不定长参数* 即可以传入任意个数的参数值* 不定长参数本质上其实是数组* -----------------------* 需要注意数组中的值的顺序需要和?的顺序一致*/int i = queryRunner.update(conn, "insert into tb_user values (?,?,?,?,?,?,?)", params);System.out.println(i );DbUtils.closeQuietly(conn);}// 查一个,封装private static void selectOne() throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2311?useSSL=false", "root", "123456");// sql执行器QueryRunner queryRunner = new QueryRunner( );// 查询结果的对象处理器,作用是将查询结果直接封装成指定对象BeanHandler<User> handler = new BeanHandler<>(User.class);/*** 执行查询调用query方法* 参数1: 数据库连接* 参数2: sql语句,如果有参数用?* 参数3: 查询结果封装处理器* 参数4: 不定长参数,对?赋值*/User user = queryRunner.query(conn, "select * from tb_user where id = ?", handler, 1);System.out.println(user );DbUtils.closeQuietly(conn);}// 查询全部,使用集合存储对象private static void selectAll() throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2311?useSSL=false", "root", "123456");// sql执行器QueryRunner queryRunner = new QueryRunner( );// 查询[多个结果]的对象处理器BeanListHandler<User> handler = new BeanListHandler<>(User.class);List<User> list = queryRunner.query(conn, "select * from tb_user", handler);list.forEach(s -> System.out.println(s ));DbUtils.closeQuietly(conn);}
}