JDBC
一、数据库驱动
类似于显卡驱动的东西，应用程序不可以直接与数据库相连，所以要用到驱动；
二、JDBC
JDBC（Java DataBase Connectivity）是Java和数据库之间的一个桥梁，==是一个规范而不是一个实现==，能够执行SQL语句。它由一组用Java语言编写的类和接口组成。各种不同类型的数据库都有相应的实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| package saxon;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class FirstDemo {
public static void main (String[] args) throws Exception{
Class.forName ("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/saxon?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName="saxon";
String password="19990707";
Connection connection = DriverManager.getConnection (url, userName, password);
Statement statement = connection.createStatement ();
String sql="SELECT * FROM student";
ResultSet resultSet = statement.executeQuery (sql);
statement.executeUpdate ();
while (resultSet.next ()){
System.out.println ("sno:"+resultSet.getObject ("sno"));
System.out.println ("sname:"+resultSet.getObject ("sname"));
System.out.println ("===========================================");
}
resultSet.close ();
statement.close ();
connection.close ();
}
}
|
步骤总结：
- 加载驱动
- 连接数据库 DrivrManager
- 创建对象 statement
- 获得结果，可以对结果进行一个操作
- 释放连接
三、关于对象的解释
1.DriverManager
1
2
3
4
5
6
7
8
9
10
11
12
13
| Class.forName ("com.mysql.cj.jdbc.Driver")
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
Connection connection = DriverManager.getConnection (url, userName, password);
|
2.URL
1
2
3
4
5
6
7
| String url="jdbc:mysql://
rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com
:3306 //端口号
/saxon //用户名
?useUnicode=true//
&characterEncoding=utf8
&useSSL=true";
|
3.statement
1
2
3
4
5
6
7
8
9
|
Statement statement = connection.createStatement ();
String sql="SELECT * FROM student";
int i = statement.executeUpdate (sql);
ResultSet resultSet = statement.executeQuery (sql);
boolean execute = statement.execute (sql);
|
4.resultSet(结果集)
1
2
3
4
5
6
7
8
9
10
|
ResultSet resultSet = statement.executeQuery (sql);
resultSet.getObject ();
resultSet.getShort ();
resultSet.getString ();
resultSet.getInt ();
resultSet.getTime ();
|
对于结果的处理：
1
2
3
4
5
| resultSet.next ();
resultSet.previous ();
resultSet.absolute (int row);
resultSet.afterLast ();
resultSet.beforeFirst ();
|
5.释放资源
消耗资源，关闭节约资源
1
2
3
| resultSet.close ();
statement.close ();
connection.close ();
|
四、statement对象
1.配置文件的设置及使用
1
2
3
4
5
|
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/saxon?useUnicode=true&characterEncoding=utf8&useSSL=true
userName=saxon
password=19990707
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
InputStream input = JdbcUtil.class.getClassLoader ().getResourceAsStream ("JDBCUTIL.properties");
Properties properties = new Properties ();
try {
properties.load (input);
String driver = properties.getProperty ("driver");
String url = properties.getProperty ("url");
String userName = properties.getProperty ("userName");
String password = properties.getProperty ("password");
} catch (Exception e) {
e.printStackTrace ();
}
|
2.工具类的书写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
| package saxon.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
static {
InputStream input = JdbcUtil.class.getClassLoader ().getResourceAsStream ("JDBCUTIL.properties");
Properties properties = new Properties ();
try {
properties.load (input);
String driver = properties.getProperty ("driver");
String url = properties.getProperty ("url");
String userName = properties.getProperty ("userName");
String password = properties.getProperty ("password");
Class.forName (driver);
} catch (Exception e) {
e.printStackTrace ();
}
}
public static Connection getConnection (String url, String userName, String password) {
Connection connection = null;
try {
connection = DriverManager.getConnection (url, userName, password);
} catch (SQLException throwables) {
throwables.printStackTrace ();
}
return connection;
}
public static void release (Connection connection, Statement statement, ResultSet resultSet) {
try {
if (connection != null) {
connection.close ();
}
if (statement != null) {
statement.close ();
}
if (resultSet != null) {
resultSet.close ();
}
} catch (Exception e) {
e.printStackTrace ();
}
}
public static void Info (Connection connection, String sql, boolean isSelect) throws Exception {
Statement statement = null;
statement = connection.createStatement ();
if (! isSelect) {
int i = statement.executeUpdate (sql);
System.out.println ("Affected rows: " + i);
statement.close ();
} else {
ResultSet resultSet = statement.executeQuery (sql);
while (resultSet.next ()) {
System.out.println ("studentno:" + resultSet.getInt ("studentno"));
System.out.println ("subjectno:" + resultSet.getInt ("subjectno"));
System.out.println ("examdate:" + resultSet.getDate ("examdate"));
System.out.println ("studentresult:" + resultSet.getInt ("studentresult"));
System.out.println ("****************************************************************");
}
statement.close ();
resultSet.close ();
}
}
}
|
3.测试
1
2
3
4
5
6
7
8
9
10
11
12
| public class TestUtil {
public static void main (String[] args)throws Exception {
String url = "jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "saxon";
String password = "19990707";
Connection connection = JdbcUtil.getConnection (url, userName, password);
String sql = "INSERT INTO result VALUES(\"1005\",\"3\",\"2020-05-14 16:00:00\",\"100\");";
String check="SELECT * FROM result";
JdbcUtil.Info (connection,sql,false);
JdbcUtil.release (connection,null,null);
}
}
|
五、SQL的注入问题
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息
1
2
|
String check="SELECT * FROM `result` WHERE `studentno`='1000' or '1==1'";
|
六、PreparedStatement对象
1、增
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| package saxon.util;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class statementTest {
public static void main (String[] args)throws Exception {
String url = "jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "saxon";
String password = "19990707";
Connection connection = JdbcUtil.getConnection (url, userName, password);
String sql = "INSERT INTO result VALUES(?,?,?,?);";
PreparedStatement statement = connection.prepareStatement (sql);
statement.setInt (1,2000);
statement.setInt (2,10);
statement.setDate (3,new Date (new java.util.Date ().getTime ()));
statement.setInt (4,10);
System.out.println (statement.executeUpdate ());
JdbcUtil.release (connection,null,null);
}
}
|
2.改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| package saxon.util;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class statementTest {
public static void main (String[] args)throws Exception {
String url = "jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "saxon";
String password = "19990707";
Connection connection = JdbcUtil.getConnection (url, userName, password);
String sql = "update result set `subjectno`=? where `studentno`=?;";
PreparedStatement statement = connection.prepareStatement (sql);
statement.setInt (1,100);
statement.setInt (2,2000);
System.out.println (statement.executeUpdate ());
JdbcUtil.release (connection,statement,null);
}
}
|
3.删除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| package saxon.util;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class statementTest {
public static void main (String[] args)throws Exception {
String url = "jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "saxon";
String password = "19990707";
Connection connection = JdbcUtil.getConnection (url, userName, password);
String sql = "delete from result where `subjectno`=?";
PreparedStatement statement = connection.prepareStatement (sql);
statement.setInt (1,2);
System.out.println (statement.executeUpdate ());
JdbcUtil.release (connection,statement,null);
}
}
|
4.查
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| package saxon.util;
import java.sql.*;
public class statementTest {
public static void main (String[] args) throws Exception {
String url = "jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "saxon";
String password = "19990707";
Connection connection = JdbcUtil.getConnection (url, userName, password);
String sql = "select * from result where `subjectno`=?";
PreparedStatement statement = connection.prepareStatement (sql);
statement.setInt (1, 4);
ResultSet resultSet = statement.executeQuery ();
while (resultSet.next ()) {
System.out.println ("studentno:" + resultSet.getInt ("studentno"));
System.out.println ("subjectno:" + resultSet.getInt ("subjectno"));
System.out.println ("examdate:" + resultSet.getDate ("examdate"));
System.out.println ("studentresult:" + resultSet.getInt ("studentresult"));
System.out.println ("****************************************************************");
}
JdbcUtil.release (connection, statement, resultSet);
}
}
|
prepareStatement可以有效的防止SQL注入的问题
七、ideal处理事务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| package saxon;
import saxon.util.JdbcUtil;
import java.sql.*;
public class transactionTest {
public static void main (String[] args) {
String url = "jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "saxon";
String password = "19990707";
Connection connection = JdbcUtil.getConnection (url, userName, password);
PreparedStatement statement=null;
try {
connection.setAutoCommit (false);
String sql="update result set `studentresult`=`studentresult`+50 where `studentno`=1004";
statement = connection.prepareStatement (sql);
statement.executeUpdate ();
int x=1/0;
String sql2="update result set `studentresult`=`studentresult`-50 where `studentno`=2000";
statement = connection.prepareStatement (sql2);
statement.executeUpdate ();
connection.commit ();
System.out.println ("successful");
} catch (Exception throwables) {
try {
connection.rollback ();
System.out.println ("失败回滚");
} catch (SQLException e) {
e.printStackTrace ();
}
throwables.printStackTrace ();
}
try {
connection.close ();
statement.close ();
} catch (SQLException throwables) {
throwables.printStackTrace ();
}
}
}
|
步骤：
- connection.setAutoCommit (false);设置自动提交
- 编写事务代码
- connection.commit ();或者 connection.rollback ();默认失败回滚，==不用关闭自动提交，在提交后默认关闭==
八、数据库连接池
池化技术：使用完的数据库连接不会消失，会进入池中，等待再次被使用，可以避免重复的回收资源(connection.close)。使用和线程池差不多
一.DBCP
需要导入的包：commons-dbcp2-2.7.0.jar,commons-logging-1.2.jar,commons-pool2-2.8.0.jar;
1.编写配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=saxon
password=19990707
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=6000
connectionProperties=useUnicode=true;characterEncoding=utf8
defaultAutoCommit=true
defaultReadOnly=
defaultTransactionIsolation=READ_COMMITTED
|
2.编写的工具类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| package saxon.util;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class dbcpUtil {
private static DataSource dataSource=null;
static {
InputStream input = JdbcUtil.class.getClassLoader ().getResourceAsStream ("dbcp.properties");
Properties properties = new Properties ();
try {
properties.load (input);
dataSource = BasicDataSourceFactory.createDataSource (properties);
} catch (Exception e) {
e.printStackTrace ();
}
}
public static Connection getConnection () throws SQLException {
return dataSource.getConnection ();
}
}
|
3.测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| package saxon;
import saxon.util.dbcpUtil;
import java.sql.Connection;
import java.sql.Statement;
public class dbcpTest {
public static void main (String[] args)throws Exception {
Connection connection = dbcpUtil.getConnection ();
Statement statement = connection.createStatement ();
System.out.println (statement.executeUpdate ("INSERT INTO result VALUES(\"1005\",\"3\",\"2020-05-14 16:00:00\",\"10086\");"));
connection.close();
statement.close();
}
}
|
二、C3P0
需要的JAR包：mchange-commons-java-0.2.11.jar，c3p0-0.9.5.1.jar；==需要注意的是，这两个包需要把版本号进行一个匹配，如果一个版本号过高就会失败==
1.配置文件；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| <c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://rm-wz917wbvou67a757quo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true
</property>
<property name="user">saxon</property>
<property name="password">19990707</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">2000</property>
<property name="maxIdleTime">1000</property>
</default-config>
</c3p0-config>
|
==文件放置在src目录下，前提是src已经被设置为sources root，否则不会成功==
==文件必须用括号包起来，括号里面一定是C3P0-config，否则读不出来==
2.工具类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| package saxon.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class c3p0Util {
private static ComboPooledDataSource dataSource=null;
static {
try {
dataSource =new ComboPooledDataSource("MYSQL");
} catch (Exception e) {
e.printStackTrace ();
}
}
public static Connection getConnection () throws SQLException {
return dataSource.getConnection ();
}
}
|
3.测试类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| package saxon;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import saxon.util.c3p0Util;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class c3p0Test {
public static void main (String[] args) {
try {
Connection connection = c3p0Util.getConnection ();
Statement statement = connection.createStatement ();
System.out.println (statement.executeUpdate ("INSERT INTO result VALUES(\"1005\",\"3\",\"2020-05-14 16:00:00\",\"100861\");"));
connection.close ();
} catch (SQLException throwables) {
throwables.printStackTrace ();
}
}
}
|
三、总结
无论是哪一种最后都实现了DataSource接口，原理不变；感觉和线程池一样；
