Java基础(三十三)JDBC(3)操作数据库
一、添加数据
在SQL语句中,一条INSERT语句只能添加一条记录,因此分为几种情况进行添加数据操作。
1.添加一条记录
(1)如果只需要添加一条记录,通常情况下通过Statament实例完成。
try { Connection conn = DriverManager.getConnection(Url, User, Password); Statement statement = conn.createStatement(); String sql = "insert into user(id, name, sex, birthday) values(4, 'winner', '男', '2018-08-07')"; int num = statement.executeUpdate(sql); System.out.println(num); // 打印:1 statement.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
(2)通过PreparedStatement实例添加单条记录时,在设置完参数值后,需要调用executeUpdate()方法。
try { Connection conn = DriverManager.getConnection(Url, User, Password); String sql = "insert into user(id, name, sex, birthday) values(?,?,?,?)"; PreparedStatement prepStatement = conn.prepareStatement(sql); prepStatement.setInt(1, 5); prepStatement.setString(2, "zigbee"); prepStatement.setString(3, "女"); prepStatement.setDate(4, new Date(System.currentTimeMillis())); int num = prepStatement.executeUpdate(); System.out.println(num); // 打印:1 prepStatement.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
(3)通过CallableStatement实例添加单条记录时,在设置完参数值后,也需要调用executeUpdate()方法。
创建包含INSERT语句的存储过程并测试
mysql> delimiter // mysql> create procedure proc_insert(IN userId INT(11), IN userName VARCHAR(255), IN userSex VARCHAR(255), IN userBir DATE ) -> BEGIN -> INSERT INTO user(id, name, sex, birthday)VALUES(userId, userName, userSex, userBir); -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> call proc_insert(6, 'tmz', '女', '1996-01-01') // Query OK, 1 row affected (0.01 sec) mysql> select * from user where id=6 // +----+------+------+------------+ | id | name | sex | birthday | +----+------+------+------------+ | 6 | tmz | 女 | 1996-01-01 | +----+------+------+------------+ 1 row in set (0.00 sec)
通过CallableStatement实例调用包含单条INSERT记录的存储过程
try { Connection conn = DriverManager.getConnection(Url, User, Password); String sql = "{call proc_insert(?,?,?,?)}"; CallableStatement callStat = conn.prepareCall(sql); callStat.setInt(1, 7); callStat.setString(2, "python"); callStat.setString(3, "女"); callStat.setDate(4, new Date(System.currentTimeMillis())); int num = callStat.executeUpdate(); System.out.println(num); callStat.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
2.添加多条记录
(1)通过Statement实例反复执行静态INSERT语句完成,但是每条SQL语句都要单独提交一次,即调用一次executeUpdate方法。
statement.executeUpdate("insert into user(id, name, sex, birthday) values(4, 'winner', '男', '2018-08-07')");
statement.executeUpdate("insert into user(id, name, sex, birthday) values(5, 'winner', '女', '2018-08-06')");
(2)通常通过PreparedStatement实例批量执行Batch中所有的动态INSERT语句完成。
try { Connection conn = DriverManager.getConnection(Url, User, Password); Object[][] records = {{8,"eight","女",new Date(System.currentTimeMillis())}, {9,"nine", "男",new Date(System.currentTimeMillis())}}; String sql = "insert into user(id, name, sex, birthday) values(?,?,?,?)"; PreparedStatement prepStatement = conn.prepareStatement(sql); prepStatement.clearBatch(); // 清空Batch for (int i = 0; i < records.length; i++) { prepStatement.setInt(1, (int) records[i][0]); prepStatement.setString(2, (String) records[i][1]); prepStatement.setString(3, (String) records[i][2]); prepStatement.setDate(4, (Date) records[i][3]); prepStatement.addBatch(); // 将INSERT语句添加到Batch中 } prepStatement.executeBatch(); // 批量执行Batch中的INSERT语句 prepStatement.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
(3)通常也可以通过CallableStatement实例多次执行只包含一条INSERT语句的存储过程完成。
同理,将上面的代码中的PreparedStatement prepStatement = conn.prepareStatement(sql);改成调用存储过程,其他均一样即可。
String sql = "{call proc_insert(?,?,?,?)}";
CallableStatement callStat = conn.prepareCall(sql);
二、查询数据
无论利用哪个实例查询数据,只要执行需要执行SELECT语句,就必须要执行executeQuery()方法,并且返回一个ResultSet类型的结果集。
(1)通过Statement实例执行静态SELECT语句完成。
ResultSet rs = statement.executeQuery("select * from user where sex='男'");
(2)通过PreparedStatement实例执行动态SELECT语句完成。
PreparedStatement predStatement = conn.prepareStatement("select * from user where sex=? ");
predStatement.setString(1, "女");
predStatement.executeQuery();
(3)通过CallableStatement实例执行包含SELECT语句的存储过程完成。
String sql = "{call proc_count_select_by_sex(?)}"; CallableStatement cablStat = conn.prepareCall(sql); cablStat.setString(1, "女");
cablStat.executeQuery();
(4)在获得查询结果集ResultSet类型的对象后,可以通过getMetaData()方法得到ResultSet实例的相关信息
ResultSet rs_queue = statement.executeQuery("select * from user where sex='男'"); ResultSetMetaData metaData = rs_queue.getMetaData(); System.out.println(metaData.getColumnCount()); // 打印:4 System.out.println(metaData.getColumnName(3)); // 打印:sex System.out.println(metaData.getColumnTypeName(3)); // 打印:VARCHAR while (rs_queue.next()) { // 遍历查询到的结果集 System.out.println(rs_queue.getInt("id") + " " + rs_queue.getString("name") + " " + rs_queue.getString("sex") + " " + rs_queue.getString("birthday")); }
三、修改数据
无论利用哪个实例修改数据,只要执行需要执行UPDATE语句,就必须要执行executeUpdate()方法,该方法将返回一个int类型的整数,是被修改记录的条数。
(1)通过Statement实例执行静态UPDATE语句完成。
statement.executeUpdate("update user set sex='女' where id=1")
(2)通过PreparedStatement实例执行动态UPDATE语句完成。
Connection conn = DriverManager.getConnection(Url, User, Password); String sql = "update user set name = ?, sex = ?, birthday = ?where id =?"; PreparedStatement predStatement = conn.prepareStatement(sql); predStatement.setString(1, "loser"); predStatement.setString(2, "女"); predStatement.setDate(3, new Date(System.currentTimeMillis())); predStatement.setInt(4, 1); System.out.println(predStatement.executeUpdate()); predStatement.close();
(3)通过CallableStatement实例执行包含UPDATE语句的存储过程完成。(略)
(4)通过PreparedStatement实例利用Batch一次执行多条UPDATE语句实现修改多条记录。(略)
四、删除数据
无论利用哪个实例删除数据,只要执行需要执行DELETE语句,就必须要执行executeUpdate()方法,该方法将返回一个int类型的整数,是被删除记录的条数。
(1)通过Statement实例执行静态DELETE语句完成。
statement.executeUpdate("delete from user where birthday<'2018-08-07'")
(2)通过PreparedStatement实例执行动态DELETE语句完成。(略)
(3)通过CallableStatement实例执行包含DELETE语句的存储过程完成。(略)
(4)通过PreparedStatement实例使用Batch一次执行多条DELETE语句实现删除多条记录。(略)
五、JDBC事务
事务是指一组相互依赖的操作单元的集合,用来保证对数据库的正确修改,保持数据的完整性,如果一个事务的某个单元操作失败,将取消本次事务的全部操作。
数据库事务的四大特征(ACID):
- 原子性(Atomic)
- 一致性(Consistency)
- 隔离性(Isolation)
- 持久性(Durability)
以银行转账为例使用JDBC实现数据库事务:
1.设置不自动提交事务
2.清空Batch
3.插入转入账户记录到Batch
4.插入转出账户记录到Batch
5.执行Batch中所有语句
6.提交此次事务
7.判断事务提交后即转账完成后,账户余额是否小于0,如果小于0,则回滚此次事务
JDBC实现数据库事务是通过Connection的实例完成的,其中包含了一些与事务相关的方法:
- setAutoCommit(false):设置为不自动提交
- getAutoCommit():查看当前是否处于自动提交模式,如果是则返回true,否则返回false。
- commit():将从上一次提交或回滚以来进行的所有更改同步到数据库
- rollback():取消当前事务中的所有更改
代码举例:
public String turnMoney(int outAccount, int inAccount, int money) { String info = "转账成功!"; Connection conn = null; try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); conn.setAutoCommit(false);// 设置为不自动提交 String sql = "insert into tb_note(account_id,operate_money,total_money,date) values(?,?,?,getdate())"; PreparedStatement prpdStmt = conn.prepareStatement(sql); prpdStmt.clearBatch(); prpdStmt.setInt(1, inAccount); prpdStmt.setInt(2, money); prpdStmt.setInt(3, getTotalMoney(inAccount) + money); prpdStmt.addBatch();// 插入转入账户记录 prpdStmt.setInt(1, outAccount); prpdStmt.setInt(2, -money); prpdStmt.setInt(3, getTotalMoney(outAccount) - money); prpdStmt.addBatch();// 插入转出账户记录 prpdStmt.executeBatch(); prpdStmt.close(); conn.commit();// 提交此次事务 } catch (SQLException e) { try { info = "转账失败,您的账户余额不足!"; conn.rollback();// 回滚此次事务 } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return info; }