JDBC入门

JDBC的概述

JDBC的全称为:Java DataBase Connectivity(java数据库连接)。

以前没有jdbc的时候，需要了解每个数据库的具体驱动模式，而现在只要了解sun公司对jdbc的一些规范即可。

JDBC的入门

1)搭建开发环境

在数据库中创建表和数据

引入数据库驱动包

将驱动包添加到构建路径当中

2)编写程序

使用Junit

点击@Test那一行前面的错误标识就可以导入JUnit相关jar包，此时demo1方法就可以像主方法一样使用了

JUnit的详细内容会在稍后介绍

 1 package com.imooc.jdbc.demo1;
 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 import org.junit.Test;
10 
11 import com.mysql.jdbc.Driver;
12 
13 public class JDBCDemo1 {
14     
15 
16     @Test
17     /**
18      * JDBC的入门程序
19      */
20     public void demo1(){
21         
22         try {
23             // 1.加载驱动
24 //            DriverManager.registerDriver(new Driver());// 会导致驱动注册两次。
25             Class.forName("com.mysql.jdbc.Driver"); 
26             // 2.获得连接
27             Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "abc");
28             // 3.创建执行SQL语句的对象，并且执行SQL
29             // 3.1创建执行sql的对象
30             String sql = "select * from user";
31             Statement stmt = conn.createStatement();
32             // 3.2执行sql
33             ResultSet resultSet = stmt.executeQuery(sql);
34             while(resultSet.next()){
35                 int uid = resultSet.getInt("uid");
36                 String username = resultSet.getString("username");
37                 String password = resultSet.getString("password");
38                 String name = resultSet.getString("name");
39                 
40                 System.out.println(uid+"   "+username+"   "+password+"   "+name);
41             }
42             // 4.释放资源
43             resultSet.close();
44             stmt.close();
45             conn.close();
46         } catch (Exception e) {
47             e.printStackTrace();
48         }
49     }
50 }
View Code

 运行程序

 运行结果

JDBC的API详解

1)DriverManager

源码

2)Connection

3)Statement

一、中int表示影响的行数

4)ResultSet

next判断是否有下一行数据，如果有next指向下一行，直到没有下一行停止。

ResultSet光标最初位于结果集第一行的前面，调用next()方法后移到第一行

当调用next()方法返回值为false时，说明光标位于最后一行之后

JDBC的资源释放

　　JDBC程序运行完后，切记要释放程序在运行过程中，创建的哪些与数据库进行交互的对象，

这些对象通常是ResultSet，Statement和Connection对象。

　　特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时、

正确的关闭，极易导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

　　为了确保资源释放代码能运行，资源释放代码也一定要放在finally语句中。

 1 package com.imooc.jdbc.demo1;
 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 import org.junit.Test;
10 
11 import com.mysql.jdbc.Driver;
12 
13 public class JDBCDemo1 {
14     
15     @Test
16     /**
17      * JDBC资源的释放
18      */
19     public void demo2(){
20         Connection conn = null;
21         Statement stmt = null;
22         ResultSet rs = null;
23         try {
24             // 1.加载驱动
25 //            DriverManager.registerDriver(new Driver());// 会导致驱动注册两次。
26             Class.forName("com.mysql.jdbc.Driver"); 
27             // 2.获得连接
28             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "abc");
29             // 3.创建执行SQL语句的对象，并且执行SQL
30             // 3.1创建执行sql的对象
31             String sql = "select * from user";
32             stmt = conn.createStatement();
33             // 3.2执行sql
34             rs = stmt.executeQuery(sql);
35             while(rs.next()){
36                 int uid = rs.getInt("uid");
37                 String username = rs.getString("username");
38                 String password = rs.getString("password");
39                 String name = rs.getString("name");
40                 
41                 System.out.println(uid+"   "+username+"   "+password+"   "+name);
42             }
43             
44         } catch (Exception e) {
45             e.printStackTrace();
46         }finally{
47             // 4.释放资源
48             if (rs != null) {
49                 try {
50                     rs.close();
51                 } catch (SQLException sqlEx) { // ignore 
52                     
53                 }
54 
55                 rs = null;
56             }
57             
58             if(stmt != null){
59                 try {
60                     stmt.close();
61                 } catch (SQLException e) {
62                     e.printStackTrace();
63                 }
64                 stmt = null;
65             }
66             
67             if(conn != null){
68                 try {
69                     conn.close();
70                 } catch (SQLException e) {
71                     e.printStackTrace();
72                 }
73                 conn = null;// 垃圾回收机制更早回收对象。
74             }
75         }
76     }
77 
78     
79 }
View Code

JDBC的CRUD操作

1）向数据库中保存记录

 1 package com.imooc.jdbc.demo1;
 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 import org.junit.Test;
10 
11 public class JDBCDemo2 {
12     
13 
14     @Test
15     /**
16      * 保存操作
17      */
18     public void demo1(){
19         Connection conn = null;
20         Statement stmt = null;
21         try{
22             // 注册驱动:
23             Class.forName("com.mysql.jdbc.Driver");
24             // 获得连接:
25             conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
26             // 获得执行SQL语句的对象:
27             stmt = conn.createStatement();
28             // 编写SQL：
29             String sql = "insert into user values (null,'eee','123','张三')";
30             // 执行SQL:
31             int i = stmt.executeUpdate(sql);
32             if(i > 0){
33                 System.out.println("保存成功！");
34             }
35         }catch(Exception e){
36             e.printStackTrace();
37         }finally{
38             // 释放资源:
39             if(stmt != null){
40                 try {
41                     stmt.close();
42                 } catch (SQLException e) {
43                     e.printStackTrace();
44                 }
45                 stmt = null;
46             }
47             if(conn != null){
48                 try {
49                     conn.close();
50                 } catch (SQLException e) {
51                     e.printStackTrace();
52                 }
53                 conn = null;
54             }
55         }
56     }
57 }
View Code

不需要结果集对象

2）修改数据库中的记录

 1 package com.imooc.jdbc.demo1;
 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 import org.junit.Test;
10 
11 public class JDBCDemo2 {
12     
13     
14     @Test
15     /**
16      * 修改操作
17      */
18     public void demo2(){
19         Connection conn = null;
20         Statement stmt = null;
21         try{
22             // 注册驱动
23             Class.forName("com.mysql.jdbc.Driver");
24             // 获得连接
25             conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
26             // 创建执行SQL语句的对象:
27             stmt = conn.createStatement();
28             // 编写SQL:
29             String sql = "update user set username = 'qqq',password='456' , name='赵六' where uid = 4";
30             // 执行SQL:
31             int i = stmt.executeUpdate(sql);
32             if(i>0){
33                 System.out.println("修改成功!");
34             }
35         }catch(Exception e){
36             e.printStackTrace();
37         }finally{
38             // 释放资源
39             if(stmt != null){
40                 try {
41                     stmt.close();
42                 } catch (SQLException e) {
43                     e.printStackTrace();
44                 }
45                 stmt = null;
46             }
47             if(conn != null){
48                 try {
49                     conn.close();
50                 } catch (SQLException e) {
51                     e.printStackTrace();
52                 }
53                 conn = null;
54             }
55         }
56     }
57 
58 }
View Code

不需要结果集对象

3）删除数据库中的记录

 1 package com.imooc.jdbc.demo1;
 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 import org.junit.Test;
10 
11 public class JDBCDemo2 {
12     
13     
14     @Test
15     /**
16      * 删除操作
17      */
18     public void demo3(){
19         Connection conn = null;
20         Statement stmt = null;
21         try{
22             // 注册驱动
23             Class.forName("com.mysql.jdbc.Driver");
24             // 获得连接
25             conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
26             // 获得执行SQL语句的对象:
27             stmt = conn.createStatement();
28             // 编写SQL:
29             String sql = "delete from user where uid = 4";
30             // 执行SQL:
31             int i = stmt.executeUpdate(sql);
32             if(i > 0){
33                 System.out.println("删除成功!");
34             }
35         }catch(Exception e){
36             e.printStackTrace();
37         }finally{
38             // 释放资源
39             if(stmt != null){
40                 try {
41                     stmt.close();
42                 } catch (SQLException e) {
43                     e.printStackTrace();
44                 }
45                 stmt = null;
46             }
47             if(conn != null){
48                 try {
49                     conn.close();
50                 } catch (SQLException e) {
51                     e.printStackTrace();
52                 }
53                 conn = null;
54             }
55         }
56     }
57     
58 }
View Code

不需要结果集对象

4）查询数据库中的记录

  1 package com.imooc.jdbc.demo1;
  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 import org.junit.Test;
 10 
 11 public class JDBCDemo2 {
 12     @Test
 13     /**
 14      * 查询一条记录
 15      */
 16     public void demo5(){
 17         Connection conn = null;
 18         Statement stmt = null;
 19         ResultSet rs = null;
 20         try{
 21             // 注册驱动
 22             Class.forName("com.mysql.jdbc.Driver");
 23             // 获得连接
 24             conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
 25             // 创建执行SQL语句的对象
 26             stmt = conn.createStatement();
 27             // 编写SQL
 28             String sql = "select * from user where uid = 1";
 29             // 执行SQL
 30             rs = stmt.executeQuery(sql);
 31             if(rs.next()){
 32                 System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
 33             }
 34         }catch(Exception e){
 35             e.printStackTrace();
 36         }finally{
 37             // 释放资源
 38             if(rs != null){
 39                 try {
 40                     rs.close();
 41                 } catch (SQLException e) {
 42                     e.printStackTrace();
 43                 }
 44                 rs = null;
 45             }
 46             if(stmt != null){
 47                 try {
 48                     stmt.close();
 49                 } catch (SQLException e) {
 50                     e.printStackTrace();
 51                 }
 52                 stmt = null;
 53             }
 54             if(conn != null){
 55                 try {
 56                     conn.close();
 57                 } catch (SQLException e) {
 58                     e.printStackTrace();
 59                 }
 60                 conn = null;
 61             }            
 62         }
 63     }
 64     
 65     @Test
 66     /**
 67      * 查询所有记录
 68      */
 69     public void demo4(){
 70         Connection conn = null;
 71         Statement stmt = null;
 72         ResultSet rs = null;
 73         try{
 74             // 注册驱动:
 75             Class.forName("com.mysql.jdbc.Driver");
 76             // 获得连接:
 77             conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc");
 78             // 创建执行SQL语句的对象:
 79             stmt = conn.createStatement();
 80             // 编写SQL:
 81             String sql = "select * from user";
 82             // 执行SQL:
 83             rs = stmt.executeQuery(sql);
 84             // 遍历结果集:
 85             while(rs.next()){
 86                 System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"    "+rs.getString("name"));
 87             }
 88         }catch(Exception e){
 89             e.printStackTrace();
 90         }finally{
 91             // 释放资源
 92             if(rs != null){
 93                 try {
 94                     rs.close();
 95                 } catch (SQLException e) {
 96                     e.printStackTrace();
 97                 }
 98                 rs = null;
 99             }
100             if(stmt != null){
101                 try {
102                     stmt.close();
103                 } catch (SQLException e) {
104                     e.printStackTrace();
105                 }
106                 stmt = null;
107             }
108             if(conn != null){
109                 try {
110                     conn.close();
111                 } catch (SQLException e) {
112                     e.printStackTrace();
113                 }
114                 conn = null;
115             }
116         }
117     }
118     
119 }
View Code

查询所有或多条记录

 查询一条记录

JDBC工具类的抽取

　　为了简化JDBC的开发，可以将一些重复的代码进行抽取。

方式一：

utils类

 1 package com.imooc.jdbc.utils;
 2 
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 import java.sql.Connection;
 6 import java.sql.DriverManager;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 import java.sql.Statement;
10 import java.util.Properties;
11 
12 /**
13  * JDBC的工具类
14  * @author jt
15  *
16  */
17 public class JDBCUtils {
18     private static final String driverClass;
19     private static final String url;
20     private static final String username;
21     private static final String password;
22     
23     static{
24         
25         driverClass = "com.mysql.jdbc.Driver";
26         url = "jdbc:mysql:///jdbctest";
27         username = "root";
28         password = "abc";
29     }
30 
31     /**
32      * 注册驱动的方法
33      * @throws ClassNotFoundException 
34      */
35     public static void loadDriver() throws ClassNotFoundException{
36         Class.forName(driverClass);
37     }
38     
39     /**
40      * 获得连接的方法:
41      * @throws SQLException 
42      */
43     public static Connection getConnection() throws Exception{
44         loadDriver();
45         Connection conn = DriverManager.getConnection(url, username, password);
46         return conn;
47     }
48     
49     /**
50      * 资源释放
51      */
52     public static void release(Statement stmt,Connection conn){
53         if(stmt != null){
54             try {
55                 stmt.close();
56             } catch (SQLException e) {
57                 e.printStackTrace();
58             }
59             stmt = null;
60         }
61         if(conn != null){
62             try {
63                 conn.close();
64             } catch (SQLException e) {
65                 e.printStackTrace();
66             }
67             conn = null;
68         }
69     }
70     
71     public static void release(ResultSet rs,Statement stmt,Connection conn){
72         if(rs!= null){
73             try {
74                 rs.close();
75             } catch (SQLException e) {
76                 e.printStackTrace();
77             }
78             rs = null;
79         }
80         if(stmt != null){
81             try {
82                 stmt.close();
83             } catch (SQLException e) {
84                 e.printStackTrace();
85             }
86             stmt = null;
87         }
88         if(conn != null){
89             try {
90                 conn.close();
91             } catch (SQLException e) {
92                 e.printStackTrace();
93             }
94             conn = null;
95         }
96     }
97 }
View Code

测试类

 1 package com.imooc.jdbc.demo3;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 import com.imooc.jdbc.utils.JDBCUtils2;
11 import com.mchange.v2.c3p0.ComboPooledDataSource;
12 
13 /**
14  * 连接池的测试类
15  * @author jt
16  *
17  */
18 public class DataSourceDemo1 {
19     
20 
21     @Test
22     /**
23      * 手动设置连接池
24      */
25     public void demo1(){
26 
27         // 获得连接:
28         Connection conn = null;
29         PreparedStatement pstmt = null;
30         ResultSet rs = null;
31         try{
32             // 创建连接池:
33             ComboPooledDataSource dataSource = new ComboPooledDataSource();
34             // 设置连接池的参数:
35             dataSource.setDriverClass("com.mysql.jdbc.Driver");
36             dataSource.setJdbcUrl("jdbc:mysql:///jdbctest");
37             dataSource.setUser("root");
38             dataSource.setPassword("abc");
39             dataSource.setMaxPoolSize(20);
40             dataSource.setInitialPoolSize(3);
41             
42             // 获得连接:
43             conn = dataSource.getConnection();
44             // 编写Sql:
45             String sql = "select * from user";
46             // 预编译SQL:
47             pstmt = conn.prepareStatement(sql);
48             // 设置参数
49             // 执行SQL:
50             rs = pstmt.executeQuery();
51             while(rs.next()){
52                 System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
53             }
54         }catch(Exception e){
55             e.printStackTrace();
56         }finally{
57             JDBCUtils.release(rs, pstmt, conn);
58         }
59     }
60 }
View Code

缺点：用户名，数据库连接url等有变化时，需要修改utils的源代码，不方便

方式二：

属性文件jdbc.properties

1 driverClass=com.mysql.jdbc.Driver
2 url=jdbc:mysql:///jdbctest
3 username=root
4 password=abc

工具类中加载属性文件,对相应的变量进行赋值

  1 package com.imooc.jdbc.utils;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.sql.Connection;
  6 import java.sql.DriverManager;
  7 import java.sql.ResultSet;
  8 import java.sql.SQLException;
  9 import java.sql.Statement;
 10 import java.util.Properties;
 11 
 12 /**
 13  * JDBC的工具类
 14  * @author jt
 15  *
 16  */
 17 public class JDBCUtils {
 18     private static final String driverClass;
 19     private static final String url;
 20     private static final String username;
 21     private static final String password;
 22     
 23     static{
 24         
 25         // 加载属性文件并解析：
 26         Properties props = new Properties();
 27         // 如何获得属性文件的输入流？
 28         // 通常情况下使用类的加载器的方式进行获取：
 29         InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
 30         try {
 31             props.load(is);
 32         } catch (IOException e) {
 33             e.printStackTrace();
 34         }
 35         
 36         driverClass = props.getProperty("driverClass");
 37         url = props.getProperty("url");
 38         username = props.getProperty("username");
 39         password = props.getProperty("password");
 40     }
 41 
 42     /**
 43      * 注册驱动的方法
 44      * @throws ClassNotFoundException 
 45      */
 46     public static void loadDriver() throws ClassNotFoundException{
 47         Class.forName(driverClass);
 48     }
 49     
 50     /**
 51      * 获得连接的方法:
 52      * @throws SQLException 
 53      */
 54     public static Connection getConnection() throws Exception{
 55         loadDriver();
 56         Connection conn = DriverManager.getConnection(url, username, password);
 57         return conn;
 58     }
 59     
 60     /**
 61      * 资源释放
 62      */
 63     public static void release(Statement stmt,Connection conn){
 64         if(stmt != null){
 65             try {
 66                 stmt.close();
 67             } catch (SQLException e) {
 68                 e.printStackTrace();
 69             }
 70             stmt = null;
 71         }
 72         if(conn != null){
 73             try {
 74                 conn.close();
 75             } catch (SQLException e) {
 76                 e.printStackTrace();
 77             }
 78             conn = null;
 79         }
 80     }
 81     
 82     public static void release(ResultSet rs,Statement stmt,Connection conn){
 83         if(rs!= null){
 84             try {
 85                 rs.close();
 86             } catch (SQLException e) {
 87                 e.printStackTrace();
 88             }
 89             rs = null;
 90         }
 91         if(stmt != null){
 92             try {
 93                 stmt.close();
 94             } catch (SQLException e) {
 95                 e.printStackTrace();
 96             }
 97             stmt = null;
 98         }
 99         if(conn != null){
100             try {
101                 conn.close();
102             } catch (SQLException e) {
103                 e.printStackTrace();
104             }
105             conn = null;
106         }
107     }
108 }
View Code

测试类

 1 package com.imooc.jdbc.demo3;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 import com.imooc.jdbc.utils.JDBCUtils2;
11 import com.mchange.v2.c3p0.ComboPooledDataSource;
12 
13 /**
14  * 连接池的测试类
15  * @author jt
16  *
17  */
18 public class DataSourceDemo1 {
19     @Test
20     /**
21      * 使用配置文件的方式
22      */
23     public void demo2(){
24         Connection conn = null;
25         PreparedStatement pstmt = null;
26         ResultSet rs = null;
27         try{
28             /*// 获得连接:
29             ComboPooledDataSource dataSource = new ComboPooledDataSource();*/
30             // 获得连接:
31             // conn = dataSource.getConnection();
32             conn = JDBCUtils2.getConnection();
33             // 编写Sql:
34             String sql = "select * from user";
35             // 预编译SQL:
36             pstmt = conn.prepareStatement(sql);
37             // 设置参数
38             // 执行SQL:
39             rs = pstmt.executeQuery();
40             while(rs.next()){
41                 System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
42             }
43         }catch(Exception e){
44             e.printStackTrace();
45         }finally{
46             JDBCUtils2.release(rs, pstmt, conn);
47         }
48     }
49 
50 }
View Code

JDBC的SQL注入漏洞

演示：

例如,用户登录界面,已知用户名,不知道密码的情况下,也可以登录到系统中,然后对账号进行一些破坏.

 1 package com.imooc.jdbc.demo2;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.Statement;
 7 
 8 import org.junit.Test;
 9 
10 import com.imooc.jdbc.utils.JDBCUtils;
11 
12 /**
13  * 演示JDBC的注入的漏洞
14  * @author jt
15  *
16  */
17 public class JDBCDemo4 {
18     @Test
19     /**
20      * 测试SQL注入漏洞的方法
21      */
22     public void demo1(){
23         boolean flag = JDBCDemo4.login2("aaa' or '1=1", "1fsdsdfsdf");
24         if(flag == true){
25             System.out.println("登录成功！");
26         }else{
27             System.out.println("登录失败！");
28         }
29     }
30     
31     /**
32      * 避免SQL注入漏洞的方法
33      */
34     public static boolean login2(String username,String password){
35         Connection conn = null;
36         PreparedStatement pstmt = null;
37         ResultSet rs = null;
38         boolean flag = false;
39         try{
40             // 获得连接:
41             conn = JDBCUtils.getConnection();
42             // 编写SQL:
43             String sql = "select * from user where username = ? and password = ?";
44             // 预处理SQL:
45             pstmt = conn.prepareStatement(sql);
46             // 设置参数:
47             pstmt.setString(1, username);
48             pstmt.setString(2, password);
49             // 执行SQL：
50             rs = pstmt.executeQuery();
51             // 判断结果街
52             if(rs.next()){
53                 flag = true;
54             }else{
55                 flag = false;
56             }
57         }catch(Exception e){
58             e.printStackTrace();
59         }finally{
60             JDBCUtils.release(rs, pstmt, conn);
61         }
62         return flag;
63     }
64     
65     /**
66      * 产生SQL注入漏洞的方法
67      * @param username
68      * @param password
69      * @return
70      */
71     public static boolean login(String username,String password){
72         Connection conn = null;
73         Statement stmt  = null;
74         ResultSet rs = null;
75         boolean flag = false;
76         try{
77             conn = JDBCUtils.getConnection();
78             // 创建执行SQL语句的对象:
79             stmt = conn.createStatement();
80             // 编写SQL:
81             String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
82             // 执行SQL:
83             rs = stmt.executeQuery(sql);
84             // 判断结果集中是否有数据。
85             if(rs.next()){
86                 flag = true;
87             }else{
88                 flag = false;
89             }
90         }catch(Exception e){
91             e.printStackTrace();
92         }finally{
93             JDBCUtils.release(rs, stmt, conn);
94         }
95         return flag;
96     }
97 }
View Code

因为在字符串中写入了sql的关键字

相当于

解决：

 1 package com.imooc.jdbc.demo2;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.Statement;
 7 
 8 import org.junit.Test;
 9 
10 import com.imooc.jdbc.utils.JDBCUtils;
11 
12 /**
13  * 演示JDBC的注入的漏洞
14  * @author jt
15  *
16  */
17 public class JDBCDemo4 {
18     @Test
19     /**
20      * 测试SQL注入漏洞的方法
21      */
22     public void demo1(){
23         boolean flag = JDBCDemo4.login2("aaa' or '1=1", "1fsdsdfsdf");
24         if(flag == true){
25             System.out.println("登录成功！");
26         }else{
27             System.out.println("登录失败！");
28         }
29     }
30     
31     /**
32      * 避免SQL注入漏洞的方法
33      */
34     public static boolean login2(String username,String password){
35         Connection conn = null;
36         PreparedStatement pstmt = null;
37         ResultSet rs = null;
38         boolean flag = false;
39         try{
40             // 获得连接:
41             conn = JDBCUtils.getConnection();
42             // 编写SQL:
43             String sql = "select * from user where username = ? and password = ?";
44             // 预处理SQL:
45             pstmt = conn.prepareStatement(sql);
46             // 设置参数:
47             pstmt.setString(1, username);
48             pstmt.setString(2, password);
49             // 执行SQL：
50             rs = pstmt.executeQuery();
51             // 判断结果街
52             if(rs.next()){
53                 flag = true;
54             }else{
55                 flag = false;
56             }
57         }catch(Exception e){
58             e.printStackTrace();
59         }finally{
60             JDBCUtils.release(rs, pstmt, conn);
61         }
62         return flag;
63     }
64     
65 }
View Code

JDBC中的PreparedStatement

1)保存数据

 1 package com.imooc.jdbc.demo2;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 
11 /**
12  * PreparedStatement的使用
13  * @author jt
14  *
15  */
16 public class JDBCDemo5 {
17 
18     @Test
19     /**
20      * 保存数据
21      */
22     public void demo1(){
23         Connection conn = null;
24         PreparedStatement pstmt = null;
25         try{
26             // 获得连接:
27             conn = JDBCUtils.getConnection();
28             // 编写SQL:
29             String sql = "insert into user values (null,?,?,?)";
30             // 预处理SQL:
31             pstmt = conn.prepareStatement(sql);
32             // 设置参数的值:
33             pstmt.setString(1, "qqq");
34             pstmt.setString(2, "123");
35             pstmt.setString(3, "张武");
36             // 执行SQL:
37             int num = pstmt.executeUpdate();
38             if(num > 0){
39                 System.out.println("保存成功！");
40             }
41         }catch(Exception e){
42             e.printStackTrace();
43         }finally{
44             // 释放资源
45             JDBCUtils.release(pstmt, conn);
46         }
47     }
48 }
View Code

2)修改数据

 1 package com.imooc.jdbc.demo2;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 
11 /**
12  * PreparedStatement的使用
13  * @author jt
14  *
15  */
16 public class JDBCDemo5 {
17     
18     @Test
19     /**
20      * 修改数据
21      */
22     public void demo2(){
23         Connection conn = null;
24         PreparedStatement pstmt = null;
25         try{
26             // 获得连接:
27             conn = JDBCUtils.getConnection();
28             // 编写SQL:
29             String sql = "update user set username = ?,password = ?,name = ? where uid = ?";
30             // 预编译SQL:
31             pstmt = conn.prepareStatement(sql);
32             // 设置参数:
33             pstmt.setString(1, "www");
34             pstmt.setString(2, "123456");
35             pstmt.setString(3, "张六");
36             pstmt.setInt(4, 6);
37             // 执行SQL:
38             int num = pstmt.executeUpdate();
39             if(num > 0){
40                 System.out.println("修改成功！");
41             }
42         }catch(Exception e){
43             e.printStackTrace();
44         }finally{
45             JDBCUtils.release(pstmt, conn);
46         }
47     }
48 
49 }
View Code

3)删除数据

 1 package com.imooc.jdbc.demo2;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 
11 /**
12  * PreparedStatement的使用
13  * @author jt
14  *
15  */
16 public class JDBCDemo5 {
17     
18     @Test
19     /**
20      * 删除数据
21      */
22     public void demo3(){
23         Connection conn = null;
24         PreparedStatement pstmt = null;
25         try{
26             // 获得连接:
27             conn = JDBCUtils.getConnection();
28             // 编写SQL：
29             String sql = "delete from user where uid = ?";
30             // 预编译SQL:
31             pstmt = conn.prepareStatement(sql);
32             // 设置参数:
33             pstmt.setInt(1, 6);
34             // 执行SQL:
35             int num = pstmt.executeUpdate();
36             if(num > 0){
37                 System.out.println("删除成功！");
38             }
39         }catch(Exception e){
40             e.printStackTrace();
41         }finally{
42             JDBCUtils.release(pstmt, conn);
43         }
44     }
45     
46 }
View Code

4)查询数据

 1 package com.imooc.jdbc.demo2;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 
11 /**
12  * PreparedStatement的使用
13  * @author jt
14  *
15  */
16 public class JDBCDemo5 {
17     @Test
18     /**
19      * 查询一条记录
20      */
21     public void demo5(){
22         Connection conn = null;
23         PreparedStatement pstmt = null;
24         ResultSet rs = null;
25         try{
26             // 获得连接:
27             conn = JDBCUtils.getConnection();
28             // 编写SQL：
29             String sql = "select * from user where uid = ?";
30             // 预编译SQL:
31             pstmt = conn.prepareStatement(sql);
32             // 设置参数:
33             pstmt.setObject(1, 3);
34             // 执行SQL:
35             rs = pstmt.executeQuery();
36             // 判断结果集:
37             if(rs.next()){
38                 System.out.println(rs.getInt("uid")+"  "+rs.getString("username")+"  "+rs.getString("password")+"  "+rs.getString("name"));
39             }
40         }catch(Exception e){
41             e.printStackTrace();
42         }finally{
43             JDBCUtils.release(rs, pstmt, conn);
44         }
45     }
46     
47     @Test
48     /**
49      * 查询所有数据
50      */
51     public void demo4(){
52         Connection conn = null;
53         PreparedStatement pstmt = null;
54         ResultSet rs = null;
55         try{
56             // 获得连接:
57             conn = JDBCUtils.getConnection();
58             // 编写SQL:
59             String sql = "select * from user";
60             // 预编译SQL:
61             pstmt = conn.prepareStatement(sql);
62             // 设置参数
63             // 执行SQL:
64             rs = pstmt.executeQuery();
65             while(rs.next()){
66                 System.out.println(rs.getInt("uid")+"  "+rs.getString("username")+"  "+rs.getString("password")+"  "+rs.getString("name"));
67             }
68         }catch(Exception e){
69             e.printStackTrace();
70         }finally{
71             JDBCUtils.release(rs, pstmt, conn);
72         }
73     }
74     
75 }
View Code

数据库连接池　　

 

 

 C3P0连接池的使用

引入连接池jar包,并添加到构建路径中

连接池测试类

1.手动设置连接池

测试类

 1 package com.imooc.jdbc.demo3;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 import com.imooc.jdbc.utils.JDBCUtils2;
11 import com.mchange.v2.c3p0.ComboPooledDataSource;
12 
13 /**
14  * 连接池的测试类
15  * @author jt
16  *
17  */
18 public class DataSourceDemo1 {
19 
20     @Test
21     /**
22      * 手动设置连接池
23      */
24     public void demo1(){
25 
26         // 获得连接:
27         Connection conn = null;
28         PreparedStatement pstmt = null;
29         ResultSet rs = null;
30         try{
31             // 创建连接池:
32             ComboPooledDataSource dataSource = new ComboPooledDataSource();
33             // 设置连接池的参数:
34             dataSource.setDriverClass("com.mysql.jdbc.Driver");
35             dataSource.setJdbcUrl("jdbc:mysql:///jdbctest");
36             dataSource.setUser("root");
37             dataSource.setPassword("abc");
38             dataSource.setMaxPoolSize(20);
39             dataSource.setInitialPoolSize(3);
40             
41             // 获得连接:
42             conn = dataSource.getConnection();
43             // 编写Sql:
44             String sql = "select * from user";
45             // 预编译SQL:
46             pstmt = conn.prepareStatement(sql);
47             // 设置参数
48             // 执行SQL:
49             rs = pstmt.executeQuery();
50             while(rs.next()){
51                 System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
52             }
53         }catch(Exception e){
54             e.printStackTrace();
55         }finally{
56             JDBCUtils.release(rs, pstmt, conn);
57         }
58     }
59 }
View Code

2. 使用配置文件方式设置连接池

c3p0-config.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <c3p0-config>
 3 
 4   <default-config>
 5     <property name="driverClass">com.mysql.jdbc.Driver</property>
 6     <property name="jdbcUrl">jdbc:mysql:///jdbctest</property>
 7     <property name="user">root</property>
 8     <property name="password">abc</property>
 9     <property name="initialPoolSize">5</property>
10     <property name="maxPoolSize">20</property>
11   </default-config>
12   
13 </c3p0-config>
View Code

测试类

 1 package com.imooc.jdbc.demo3;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 
 7 import org.junit.Test;
 8 
 9 import com.imooc.jdbc.utils.JDBCUtils;
10 import com.imooc.jdbc.utils.JDBCUtils2;
11 import com.mchange.v2.c3p0.ComboPooledDataSource;
12 
13 /**
14  * 连接池的测试类
15  * @author jt
16  *
17  */
18 public class DataSourceDemo1 {
19     @Test
20     /**
21      * 使用配置文件的方式
22      */
23     public void demo2(){
24         Connection conn = null;
25         PreparedStatement pstmt = null;
26         ResultSet rs = null;
27         try{
28             /*// 获得连接:
29             ComboPooledDataSource dataSource = new ComboPooledDataSource();*/
30             // 获得连接:
31             // conn = dataSource.getConnection();
32             conn = JDBCUtils2.getConnection();
33             // 编写Sql:
34             String sql = "select * from user";
35             // 预编译SQL:
36             pstmt = conn.prepareStatement(sql);
37             // 设置参数
38             // 执行SQL:
39             rs = pstmt.executeQuery();
40             while(rs.next()){
41                 System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
42             }
43         }catch(Exception e){
44             e.printStackTrace();
45         }finally{
46             JDBCUtils2.release(rs, pstmt, conn);
47         }
48     }
49 
50 }
View Code

工具类

 1 package com.imooc.jdbc.utils;
 2 
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 import java.sql.Connection;
 6 import java.sql.DriverManager;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 import java.sql.Statement;
10 import java.util.Properties;
11 
12 import com.mchange.v2.c3p0.ComboPooledDataSource;
13 
14 /**
15  * JDBC的工具类
16  * @author jt
17  *
18  */
19 public class JDBCUtils2 {
20     private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
21     
22     /**
23      * 获得连接的方法:
24      * @throws SQLException 
25      */
26     public static Connection getConnection() throws Exception{
27         Connection conn = dataSource.getConnection();
28         return conn;
29     }
30     
31     /**
32      * 资源释放
33      */
34     public static void release(Statement stmt,Connection conn){
35         if(stmt != null){
36             try {
37                 stmt.close();
38             } catch (SQLException e) {
39                 e.printStackTrace();
40             }
41             stmt = null;
42         }
43         if(conn != null){
44             try {
45                 conn.close();
46             } catch (SQLException e) {
47                 e.printStackTrace();
48             }
49             conn = null;
50         }
51     }
52     
53     public static void release(ResultSet rs,Statement stmt,Connection conn){
54         if(rs!= null){
55             try {
56                 rs.close();
57             } catch (SQLException e) {
58                 e.printStackTrace();
59             }
60             rs = null;
61         }
62         if(stmt != null){
63             try {
64                 stmt.close();
65             } catch (SQLException e) {
66                 e.printStackTrace();
67             }
68             stmt = null;
69         }
70         if(conn != null){
71             try {
72                 conn.close();
73             } catch (SQLException e) {
74                 e.printStackTrace();
75             }
76             conn = null;
77         }
78     }
79 }
View Code

 

posted @ 2020-04-12 19:04  阿江是个程序猿  阅读(181)  评论(0)  编辑  收藏  举报