JDBC_hm

 

添加包：mysql-connector-java-5.1.46-bin.jar

Statement操作CRUD

示例：使用Statement查询数据表

 1 public class Demo {
 2     public static void main(String[] args) {
 3         Connection conn = null ;
 4         Statement st = null ;
 5         ResultSet rs = null ;
 6         try {
 7             //注册驱动
 8             DriverManager.registerDriver(new Driver());
 9             //链接数据库
10             String url = "jdbc:mysql://localhost/hm_study" ;
11             String user = "root" ;
12             String password = "tiger" ;
13             conn = DriverManager.getConnection(url, user, password) ;
14             //
15             String sql = "select * from stu" ;
16             st = conn.createStatement() ;
17             rs = st.executeQuery(sql) ;
18             while(rs.next()) {//遍历查询结果集
19                 int id = rs.getInt("id") ;
20                 String name = rs.getString("name") ;
21                 int age = rs.getInt("age") ;
22                 System.out.println("id:"+id+",name:"+name+",age:"+age);
23             }
24         } catch (SQLException e) {
25             e.printStackTrace();
26         }finally {//关闭相关资源
27             if(rs != null) {
28                 try {
29                     rs.close();
30                 } catch (SQLException e) {
31                     e.printStackTrace();
32                 }
33             }
34             if(st != null) {
35                 try {
36                     st.close();
37                 } catch (SQLException e) {
38                     e.printStackTrace();
39                 }
40             }
41             if(conn != null) {
42                 try {
43                     conn.close();
44                 } catch (SQLException e) {
45                     e.printStackTrace();
46                 }
47             }
48         }
49     }
50 }
View Code

 由于Driver类中的静态代码块已经有注册驱动的代码了，故无需自己使用DriverManager注册驱动

 

以下三个示例只是SQL语句部分的字符串不同

示例：添加数据记录

 1     private static void demo2() {
 2         // 添加记录
 3         InputStream is = null;
 4         Connection conn = null;
 5         Statement st = null;
 6         try {
 7             is = Demo.class.getClassLoader().getResourceAsStream("jdbc.properties");
 8             Properties properties = new Properties();
 9             properties.load(is);
10             String driverClass = properties.getProperty("driverClass");
11             String url = properties.getProperty("url");
12             String username = properties.getProperty("username");
13             String password = properties.getProperty("password");
14             Class.forName(driverClass);
15             conn = DriverManager.getConnection(url, username, password);
16 
17             st = conn.createStatement();
18             String sql = "insert into stu (id,name,age) values(5,'java',24)";
19             int result = st.executeUpdate(sql);
20             if (result > 0) {
21                 System.out.println("添加成功");
22             } else {
23                 System.out.println("添加失败");
24             }
25         } catch (IOException e) {
26             e.printStackTrace();
27         } catch (ClassNotFoundException e) {
28             e.printStackTrace();
29         } catch (SQLException e) {
30             e.printStackTrace();
31         } finally {
32             if (st != null) {
33                 try {
34                     st.close();
35                 } catch (SQLException e) {
36                     e.printStackTrace();
37                 }
38             }
39             if (conn != null) {
40                 try {
41                     conn.close();
42                 } catch (SQLException e) {
43                     e.printStackTrace();
44                 }
45             }
46         }
47     }
View Code

 

 

示例：更新数据记录

 1     private static void demo3() {
 2         // 更新记录
 3         InputStream is = null;
 4         Connection conn = null;
 5         Statement st = null;
 6         try {
 7             is = Demo.class.getClassLoader().getResourceAsStream("jdbc.properties");
 8             Properties properties = new Properties();
 9             properties.load(is);
10             String driverClass = properties.getProperty("driverClass");
11             String url = properties.getProperty("url");
12             String username = properties.getProperty("username");
13             String password = properties.getProperty("password");
14             Class.forName(driverClass);
15             conn = DriverManager.getConnection(url, username, password);
16 
17             st = conn.createStatement();
18             String sql = "update stu set name='时间' where id=3";
19             int result = st.executeUpdate(sql);
20             if (result > 0) {
21                 System.out.println("更新成功");
22             } else {
23                 System.out.println("更新失败");
24             }
25         } catch (IOException e) {
26             e.printStackTrace();
27         } catch (ClassNotFoundException e) {
28             e.printStackTrace();
29         } catch (SQLException e) {
30             e.printStackTrace();
31         } finally {
32             if (st != null) {
33                 try {
34                     st.close();
35                 } catch (SQLException e) {
36                     e.printStackTrace();
37                 }
38             }
39             if (conn != null) {
40                 try {
41                     conn.close();
42                 } catch (SQLException e) {
43                     e.printStackTrace();
44                 }
45             }
46         }
47     }
View Code

 

 

示例：删除数据记录

 1     private static void demo4() {
 2         // 删除记录
 3         InputStream is = null;
 4         Connection conn = null;
 5         Statement st = null;
 6         try {
 7             is = Demo.class.getClassLoader().getResourceAsStream("jdbc.properties");
 8             Properties properties = new Properties();
 9             properties.load(is);
10             String driverClass = properties.getProperty("driverClass");
11             String url = properties.getProperty("url");
12             String username = properties.getProperty("username");
13             String password = properties.getProperty("password");
14             Class.forName(driverClass);
15             conn = DriverManager.getConnection(url, username, password);
16 
17             st = conn.createStatement();
18             String sql = "delete from stu where id=2";
19             int result = st.executeUpdate(sql);
20             if (result > 0) {
21                 System.out.println("删除成功");
22             } else {
23                 System.out.println("删除失败");
24             }
25         } catch (IOException e) {
26             e.printStackTrace();
27         } catch (ClassNotFoundException e) {
28             e.printStackTrace();
29         } catch (SQLException e) {
30             e.printStackTrace();
31         } finally {
32             if (st != null) {
33                 try {
34                     st.close();
35                 } catch (SQLException e) {
36                     e.printStackTrace();
37                 }
38             }
39             if (conn != null) {
40                 try {
41                     conn.close();
42                 } catch (SQLException e) {
43                     e.printStackTrace();
44                 }
45             }
46         }
47     }
View Code

 

 

PrepareStatement

示例：登录（查询数据）

 1     public static void login(String username,String password) {
 2         Connection conn = null ;
 3         PreparedStatement ps = null ;
 4         ResultSet rs = null ;
 5         InputStream is = Demo2.class.getClassLoader().getResourceAsStream("jdbc.properties") ;
 6         Properties properties = new Properties() ;
 7         try {
 8             properties.load(is);
 9             String driverClass = properties.getProperty("driverClass") ;
10             String url = properties.getProperty("url") ;
11             String user = properties.getProperty("username") ;
12             String userPassword = properties.getProperty("password") ;
13             Class.forName(driverClass) ;
14             conn = DriverManager.getConnection(url, user, userPassword) ;
15             
16             String sql = "select * from user where username=? and password=?" ;
17             ps = conn.prepareStatement(sql) ;
18             ps.setString(1, username);
19             ps.setString(2, password);
20             
21             rs = ps.executeQuery() ;
22             if(rs.next()) {
23                 System.out.println("登录成功");
24             }else {
25                 System.out.println("登录失败");
26             }
27             
28         } catch (IOException e) {
29             e.printStackTrace();
30         } catch (ClassNotFoundException e) {
31             e.printStackTrace();
32         } catch (SQLException e) {
33             e.printStackTrace();
34         }finally {
35             if(rs != null) {
36                 try {
37                     rs.close();
38                 } catch (SQLException e) {
39                     e.printStackTrace();
40                 }
41             }
42             if(ps != null) {
43                 try {
44                     ps.close();
45                 } catch (SQLException e) {
46                     e.printStackTrace();
47                 }
48             }
49             if(conn != null) {
50                 try {
51                     conn.close();
52                 } catch (SQLException e) {
53                     e.printStackTrace();
54                 }
55             }
56         }
57     }
View Code

 

 

数据的添加、删除、更新，其中不同的只是SQL语句部分

示例：注册（插入数据）

 1     public static void regist(String username,String password) {
 2         Connection conn = null ;
 3         PreparedStatement ps = null ;
 4         ResultSet rs = null ;
 5         InputStream is = Demo2.class.getClassLoader().getResourceAsStream("jdbc.properties") ;
 6         Properties properties = new Properties() ;
 7         try {
 8             properties.load(is);
 9             String driverClass = properties.getProperty("driverClass") ;
10             String url = properties.getProperty("url") ;
11             String user = properties.getProperty("username") ;
12             String userPassword = properties.getProperty("password") ;
13             Class.forName(driverClass) ;
14             conn = DriverManager.getConnection(url, user, userPassword) ;
15             
16             String sql = "insert into user(username,password) values(?,?)" ;
17             ps = conn.prepareStatement(sql) ;
18             ps.setString(1, username);
19             ps.setString(2, password);
20             
21             int result = ps.executeUpdate() ;
22             if(result > 0) {
23                 System.out.println("注册成功");
24             }else {
25                 System.out.println("注册失败");
26             }
27             
28         } catch (IOException e) {
29             e.printStackTrace();
30         } catch (ClassNotFoundException e) {
31             e.printStackTrace();
32         } catch (SQLException e) {
33             e.printStackTrace();
34         }finally {
35             if(rs != null) {
36                 try {
37                     rs.close();
38                 } catch (SQLException e) {
39                     e.printStackTrace();
40                 }
41             }
42             if(ps != null) {
43                 try {
44                     ps.close();
45                 } catch (SQLException e) {
46                     e.printStackTrace();
47                 }
48             }
49             if(conn != null) {
50                 try {
51                     conn.close();
52                 } catch (SQLException e) {
53                     e.printStackTrace();
54                 }
55             }
56         }
57     }
View Code

 

示例：删除数据

 1     public static void deleteUser(int id) {
 2         Connection conn = null ;
 3         PreparedStatement ps = null ;
 4         ResultSet rs = null ;
 5         InputStream is = Demo2.class.getClassLoader().getResourceAsStream("jdbc.properties") ;
 6         Properties properties = new Properties() ;
 7         try {
 8             properties.load(is);
 9             String driverClass = properties.getProperty("driverClass") ;
10             String url = properties.getProperty("url") ;
11             String user = properties.getProperty("username") ;
12             String userPassword = properties.getProperty("password") ;
13             Class.forName(driverClass) ;
14             conn = DriverManager.getConnection(url, user, userPassword) ;
15             
16             String sql = "delete from user where id=?" ;
17             ps = conn.prepareStatement(sql) ;
18             ps.setInt(1, id);
19             
20             int result = ps.executeUpdate() ;
21             if(result > 0) {
22                 System.out.println("删除成功");
23             }else {
24                 System.out.println("删除失败");
25             }
26             
27         } catch (IOException e) {
28             e.printStackTrace();
29         } catch (ClassNotFoundException e) {
30             e.printStackTrace();
31         } catch (SQLException e) {
32             e.printStackTrace();
33         }finally {
34             if(rs != null) {
35                 try {
36                     rs.close();
37                 } catch (SQLException e) {
38                     e.printStackTrace();
39                 }
40             }
41             if(ps != null) {
42                 try {
43                     ps.close();
44                 } catch (SQLException e) {
45                     e.printStackTrace();
46                 }
47             }
48             if(conn != null) {
49                 try {
50                     conn.close();
51                 } catch (SQLException e) {
52                     e.printStackTrace();
53                 }
54             }
55         }
56     }
View Code

 

 

 

zwy

posted @ 2019-03-06 07:42  zwyk  阅读(54)  评论(0)    收藏  举报