SDUT Java---jdbc

8-1 sdut-JDBC-1 实现数据库表的CRUD操作

import java.sql.*; public class Main { public static void main(String[] args) throws ClassNotFoundException, SQLException { Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/school?useunicode=true&characterEncoding=utf-8", "root", "123456"); //数据库服务器名称（地址)、端口号、数据库名称、用户名、密码须根据实际情况改变 Statement st = con.createStatement(); //向表中增加记录并显示所有记录（数据自己指定）； String insertSQL1 = "insert into student values(null,'嘿嘿',86);"; String insertSQL2 = "insert into student values(null,'哈哈',99);"; String insertSQL3 = "insert into student values(null,'呼呼',88);"; st.executeUpdate(insertSQL1); st.executeUpdate(insertSQL2); st.executeUpdate(insertSQL3); System.out.println("--1.增加-------"); String selectall = "Select * from student"; ResultSet rs = st.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } //从表中删除id=1的记录，并显示所有记录； String deletSQL = "delete from student where id=1"; st.executeUpdate(deletSQL); System.out.println("--2.删除---"); rs = st.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } //修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录； System.out.println("\n--3.修改---"); String updateSQL = "update student set name='山东理工' where id=2"; st.executeUpdate(updateSQL); rs = st.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } //查询表中id=3的记录并显示。 System.out.println("\n-4.条件查询-----"); String selectSQL = "select * from student where id=3"; rs = st.executeQuery(selectSQL); if(rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); double score = rs.getDouble("score"); System.out.println(id + " " + name + " " + score); } } }

8-2 sdut-JDBC-2 实现数据库表的CRUD操作_中级（PreparedStatement)

import java.sql.*; public class Main { public static void main(String[] args) throws SQLException { Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/school?useunicode=true&characterEncoding=utf-8", "root", "123456"); //数据库服务器名称（地址)、端口号、数据库名称、用户名、密码须根据实际情况改变 PreparedStatement pst = null; //向表中增加记录（id列自增，可只考虑姓名和成绩），并显示所有记录； String insertSQL = "insert into student values(null, ?, ?)"; pst = con.prepareStatement(insertSQL); pst.setString(1, "ANNa"); pst.setDouble(2, 86.5); pst.executeUpdate(); System.out.println("--1.插入------"); String selectall = "Select * from student"; ResultSet rs = pst.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } //从表中删除id=? 的记录，并显示所有记录； String deleteSQL = "delete from student where id=?"; pst = con.prepareStatement(deleteSQL); pst.setInt(1, 1); pst.executeUpdate(); System.out.println("\n--2.删除-----"); rs = pst.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } //修改表中记录：查询条件id=?，将name修改为：?，修改完毕显示所有记录； String updateSQL = "update student set name=? where id=?"; pst = con.prepareStatement(updateSQL); pst.setString(1, "山东理工"); pst.setInt(2, 2); pst.executeUpdate(); System.out.println("\n--3.修改-----"); rs = pst.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } //查询表中id=? 的记录并显示。 String selectSQL = "select * from student where id=?"; pst = con.prepareStatement(selectSQL); pst.setInt(1, 3); rs = pst.executeQuery(); System.out.println("\n--4.查询-----"); if(rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); double score = rs.getDouble("score"); System.out.println(id + " " + name + " " + score); } rs.close(); pst.close(); con.close(); } }

8-3 sdut-JDBC-3 实现数据库表的CRUD操作_中级（事务)

import java.sql.*; public class Main { public static void main(String[] args) throws SQLException { Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/school?useunicode=true&characterEncoding=utf-8", "root", "123456"); //数据库服务器名称（地址)、端口号、数据库名称、用户名、密码须根据实际情况改变 con.setAutoCommit(false); PreparedStatement pst = null; //向表中增加1条记录，id列自增，可只考虑姓名和成绩列的数据， String insertSQL = "insert into student values(null, ?, ?)"; pst = con.prepareStatement(insertSQL); pst.setString(1, "CHRISE"); pst.setDouble(2, 86.5); int result1 = pst.executeUpdate(); //从表中删除id=? 的记录； String deleteSQL = "delete from student where id=?"; pst = con.prepareStatement(deleteSQL); pst.setInt(1, 2); int result2 = pst.executeUpdate(); int result = result1*result2; System.out.println(result == 1 ? "增加记录和删除记录成功" : "增加记录和删除记录失败"); if(result == 1) { con.commit(); } else { con.rollback(); } // 显示所有 System.out.println("\n---表中记录为--"); String selectall = "select * from student"; ResultSet rs = pst.executeQuery(selectall); while(rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); double score = rs.getDouble(3); System.out.println(id + " " + name + " " + score); } rs.close(); pst.close(); con.close(); } }