JDBC学习笔记：CRUD

时间：2022-03-14 02:15

1.建立数据库

 1 --创建数据库
 2 create database jdbc;
 3 
 4 --选择调用jdbc数据库
 5 use jdbc;
 6 
 7 --创建user表，id:设置为主键 
 8 create table user
 9 (
10 id    integer not null auto_increment primary key,
11 name varchar(45) not null,
12 birthday Date,
13 money float
14 );
15 
16 --向表中插入数据
17 insert into user(name,birthday,money)
18 values(‘zhangsan‘, ‘1985-01-01‘, 100),
19 (‘lisi‘, ‘1986-01-01‘, 200),
20 (‘wangwu‘, ‘1987-01-01‘, 300);

2.JDBC操作的基本步骤

　　(1)加载驱动：只需加载一次

　　(2)建立连接：建立与数据库的连接，DriverManager.getConnection(url,username,password); url格式：jdbc:协议名称:子名称//主机地址:端口号/数据库名称 username：数据库用户名 password：密码

　　(3)创建语句

　　(4)执行语句

　　(5)处理执行结果

　　(6)关闭连接，释放资源

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 
 7 public class Demo {
 8     
 9     public static void main(String[] args) throws ClassNotFoundException, SQLException {
10         // 加载驱动
11         Class.forName("com.mysql.jdbc.Driver");
12         
13         // 建立连接  url格式 - jdbc:子协议:子名称//主机名:端口/数据库名？属性名=属性值&…
14         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","1234");
15                 
16         // 创建语句 
17         Statement st = conn.createStatement();
18         
19         // 执行语句
20         ResultSet rs = st.executeQuery("select * from user");
21         
22         // 处理结果，打印user表中的id字段
23         while(rs.next()) {
24             System.out.println(rs.getInt("id"));
25         }
26         
27         //关闭连接，释放资源
28         rs.close();
29         st.close();
30         conn.close();
31     }
32 }

【运行结果】：

1    zhangsan
2    lisi
3    wangwu

3.自建工具包

(1)创建连接和释放资源的工具包

 1  2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 public final class JdbcUtils {
10     
11     private static String url = "jdbc:mysql://localhost:3306/jdbc";
12     private static String user = "root";
13     private static String password = "1234";
14     
15     private JdbcUtils(){}
16     
17     static {
18         try {
19             Class.forName("com.mysql.jdbc.Driver");
20         } catch (ClassNotFoundException e) {
21             throw new ExceptionInInitializerError(e);
22         }
23     }
24     
25     public static Connection getConnection() throws SQLException {
26         return DriverManager.getConnection(url, user, password);
27     }
28     
29     public static void free(ResultSet rs, Statement st, Connection conn) {
30         try {
31             if (rs != null) {
32                 rs.close();
33             }
34         } catch (SQLException e) {
35             e.printStackTrace();
36         } finally {
37             try {
38                 if (st != null) 
39                     st.close();
40             } catch (SQLException e) {
41                 e.printStackTrace();
42             } finally {
43                 if (conn != null)
44                     try {
45                         conn.close();
46                     } catch (SQLException e) {
47                         e.printStackTrace();
48                     }                
49             }
50         }
51     }
52 }

(2)利用单利设计模式创建的工具包，用于建立连接和释放资源

 1 
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 public class JdbcUtilsSing {
10     
11     private String url = "jdbc:mysql://localhost:3306/jdbc";
12     private String user = "root";
13     private String password = "1234";
14     
15     private static JdbcUtilsSing instance = null;
16     
17     private JdbcUtilsSing() {
18         
19     }
20     
21     public static JdbcUtilsSing getInstance() {
22         
23         if (instance == null) {
24             synchronized(JdbcUtilsSing.class) {
25                 if (instance == null)
26                     instance = new JdbcUtilsSing();
27             }            
28         }
29         return instance;
30     }
31     
32     static {
33         try {
34             Class.forName("com.mysql.jdbc.Driver");
35         } catch (ClassNotFoundException e) {
36             throw new ExceptionInInitializerError(e);
37         }
38     }
39     
40     public Connection getConnection() throws SQLException {
41         return DriverManager.getConnection(url, user, password);
42     }
43     
44     public void free(ResultSet rs, Statement st, Connection conn) {
45         try {
46             if (rs != null) {
47                 rs.close();
48             }
49         } catch (SQLException e) {
50             e.printStackTrace();
51         } finally {
52             try {
53                 if (st != null) 
54                     st.close();
55             } catch (SQLException e) {
56                 e.printStackTrace();
57             } finally {
58                 if (conn != null)
59                     try {
60                         conn.close();
61                     } catch (SQLException e) {
62                         e.printStackTrace();
63                     }                
64             }
65         }
66     }
67 }

4.利用Junit测试CRUD操作

(1)create

 1     @Test
 2     public void create() throws SQLException {
 3         Connection conn = null;
 4         Statement st = null;
 5         ResultSet rs = null;
 6         try {
 7             conn = JdbcUtils.getConnection();
 8             st = conn.createStatement();
 9             String sql = "insert into user(name,birthday,money) values(‘name 1‘,‘1987-01-01‘,400)";
10             int i = st.executeUpdate(sql);
11             System.out.println("i = " + i);
12         } finally {
13             JdbcUtils.free(rs, st, conn);
14         }
15     }

(2)read

 1     @Test
 2     public void read() throws SQLException {
 3         Connection conn = null;
 4         Statement st = null;
 5         ResultSet rs = null;
 6         try {
 7             conn = JdbcUtils.getConnection();
 8             st = conn.createStatement();
 9             rs = st.executeQuery("select id,name,birthday,money from user");
10             
11             while(rs.next()) {
12                 System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" 
13                         + rs.getDate("birthday"));
14             }
15         } finally {
16             JdbcUtils.free(rs, st, conn);
17         }
18     }

(3)update

 1     @Test
 2     public void update() throws SQLException {
 3         Connection conn = null;
 4         Statement st = null;
 5         ResultSet rs = null;
 6         try {
 7             conn = JdbcUtils.getConnection();
 8             st = conn.createStatement();
 9             String sql = "update user set money=money+100";
10             int i = st.executeUpdate(sql);
11             System.out.println("i = " + i);
12         } finally {
13             JdbcUtils.free(rs, st, conn);
14         }
15     }

(4)delete

 1     @Test
 2     public void delete() throws SQLException {
 3         Connection conn = null;
 4         Statement st = null;
 5         ResultSet rs = null;
 6         try {
 7             conn = JdbcUtils.getConnection();
 8             st = conn.createStatement();
 9             String sql = "delete from user where id > 3";
10             int i = st.executeUpdate(sql);
11             System.out.println("i = " + i);
12         } finally {
13             JdbcUtils.free(rs, st, conn);
14         }
15     }