前言

其實(shí)學(xué)Mybatis前就該學(xué)了，但是尋思目前主流框架都是用mybatis和mybatis-plus就沒再去看，結(jié)果在代碼審計(jì)中遇到了很多cms是使用jdbc的因此還是再學(xué)一下吧。

第一個(gè)JDBC程序

sql文件

INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES (1, 'zhansan', '123456', 'zs@sina.com', '1980-12-04');
INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES (2, 'lisi', '123456', 'lisi@sina.com', '1981-12-04');
INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES (3, 'wangwu', '123456', 'wangwu@sina.com', '1979-12-04');

HelloJDBC

import java.sql.*;public class HelloJDBC {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1. 加載驅(qū)動(dòng)Class.forName("com.mysql.jdbc.Driver");//2. 用戶信息和urlString url = "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";String name = "root";String password = "123456";//3. 連接數(shù)據(jù)庫 connection是數(shù)據(jù)庫對(duì)象Connection connection = DriverManager.getConnection(url, name, password);//4. 執(zhí)行sql的對(duì)象 statement是執(zhí)行sql的對(duì)象Statement statement = connection.createStatement();//5. 用statement對(duì)象執(zhí)行sql語句String sql = "select * from users";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){System.out.println("id:"+resultSet.getObject("id")+",name:"+resultSet.getObject("name")+",password:"+resultSet.getObject("password"));System.out.println("=============================");}//6.釋放資源resultSet.close();statement.close();connection.close();}
}

Statement對(duì)象

工具類

package utils;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JdbcUtils {public static Connection connection() throws ClassNotFoundException, SQLException, IOException {InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties properties = new Properties();properties.load(in);String driver = properties.getProperty("driver");String url = properties.getProperty("url");String username = properties.getProperty("username");String password = properties.getProperty("password");Class.forName(driver);return DriverManager.getConnection(url,username,password);}public static void relese(ResultSet resultSet, Statement statement, Connection connection) throws SQLException {if (resultSet!=null){resultSet.close();}if (statement!=null){statement.close();}if (connection!=null){connection.close();}}
}

Demo

import utils.JdbcUtils;import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class JdbcStatement {public static void main(String[] args) throws Exception {
//        query();insert();}public static void query() throws SQLException, ClassNotFoundException, IOException{Connection connection = JdbcUtils.connection();String sql = "select * from users";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while(resultSet.next()){System.out.println("id:"+resultSet.getObject("id"));}JdbcUtils.relese(resultSet,statement,connection);}public static void insert() throws Exception{Connection connection = JdbcUtils.connection();String sql = "insert into users values(4,'Sentiment',123456,'Sentiment@qq.com','1980-12-04')";Statement statement = connection.createStatement();int i = statement.executeUpdate(sql);System.out.println(i);JdbcUtils.relese(null,statement,connection);}
}

查詢用executeQuery()，增、刪、改用executeUpdate()

PreparedStatement對(duì)象

用statement的話會(huì)有sql注入問題，因此可以用preparedstatement進(jìn)行預(yù)處理來進(jìn)行防御

主要是通過占位符來執(zhí)行查詢語句

public class JdbcPreparedStatement {public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {Connection connection = JdbcUtils.connection();String sql = "insert into users values(?,?,?,null,null)";PreparedStatement ps = connection.prepareStatement(sql);ps.setInt(1,5);ps.setString(2,"Sentiment");ps.setInt(3,123456);ps.execute();}
}

操作事務(wù)

mybatis中學(xué)過，不過連含義都忘了。。。。

其實(shí)就是執(zhí)行語句時(shí)要不都成功執(zhí)行，一個(gè)不能執(zhí)行則全部都不執(zhí)行

主要就是關(guān)閉自動(dòng)提交事務(wù)

connection.setAutoCommit(false); //開啟事務(wù)

Demo

  PreparedStatement ps = null;Connection connection = null;try {connection = JdbcUtils.connection();//關(guān)閉數(shù)煙庫的自動(dòng)提交，自動(dòng)會(huì)開啟事務(wù)connectionconnection.setAutoCommit(false); //開啟事務(wù)String sql1 = "update users set name = 'Sentiment' where id=3";ps = connection.prepareStatement(sql1);ps.executeUpdate();int x = 1 / 0;String sql2 = "update users set name = 'Sentiment' where id=1";ps = connection.prepareStatement(sql2);ps.executeUpdate();connection.commit();System.out.println("Success!");}catch (SQLException e){connection.rollback();      //執(zhí)行失敗后，事務(wù)回滾}finally {JdbcUtils.relese(null,ps,connection);}}

數(shù)據(jù)庫連接池

在上述工具類中，是通過以下方法來獲取配置文件參數(shù)，并連接連接池

String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Class.forName(driver);
return DriverManager.getConnection(url,username,password);

而我們可以用開源的數(shù)據(jù)庫連接池如DBCP、C3P0、Druid等

使用了這些數(shù)據(jù)庫連接池之后，我們?cè)陧?xiàng)目開發(fā)中就不需要編寫連接數(shù)據(jù)庫的代碼了!

DBCP

<dependency><groupId>commons-dbcp</groupId><artifactId>commons-dbcp</artifactId><version>1.4</version>
</dependency><dependency><groupId>commons-pool</groupId><artifactId>commons-pool</artifactId><version>1.5.4</version>
</dependency>

配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456#<!-- 初始化連接 -->
initialSize=10#最大連接數(shù)量
maxActive=50#<!-- 最大空閑連接 -->
maxIdle=20#<!-- 最小空閑連接 -->
minIdle=5#<!-- 超時(shí)等待時(shí)間以毫秒為單位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驅(qū)動(dòng)建立連接時(shí)附帶的連接屬性屬性的格式必須為這樣：【屬性名=property;】
#注意："user" 與 "password" 兩個(gè)屬性會(huì)被明確地傳遞，因此這里不需要包含他們。
connectionProperties=useUnicode=true;characterEncoding=utf8#指定由連接池所創(chuàng)建的連接的自動(dòng)提交（auto-commit）狀態(tài)。
defaultAutoCommit=true#driver default 指定由連接池所創(chuàng)建的連接的只讀（read-only）狀態(tài)。
#如果沒有設(shè)置該值，則“setReadOnly”方法將不被調(diào)用。（某些驅(qū)動(dòng)并不支持只讀模式，如：Informix）
defaultReadOnly=true#driver default 指定由連接池所創(chuàng)建的連接的事務(wù)級(jí)別（TransactionIsolation）。
#可用值為下列之一：（詳情可見javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

Demo

使用DBCP后，utils就可以簡化為：

public static Connection connection() throws Exception {InputStream in = DBCP_Utils.class.getClassLoader().getResourceAsStream("dbcp.properties");Properties properties = new Properties();properties.load(in);//創(chuàng)建數(shù)據(jù)源DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);return dataSource.getConnection();
}

讀取配置文件，交給數(shù)據(jù)源即可

C3P0

這個(gè)更簡單

<dependency><groupId>com.mchange</groupId><artifactId>c3p0</artifactId><version>0.9.5.5</version>
</dependency>
<dependency><groupId>com.mchange</groupId><artifactId>mchange-commons-java</artifactId><version>0.2.19</version>
</dependency>

配置文件

這里設(shè)置了兩個(gè)數(shù)據(jù)源：

<default-config>:默認(rèn)值創(chuàng)建數(shù)據(jù)源時(shí)不需要形參，ComboPooledDataSource ds=new ComboPooledDataSource();

<named-config name="MySQL">: 非默認(rèn)要指定數(shù)據(jù)源，ComboPooledDataSource ds=new ComboPooledDataSource(“MySQL”);

<?xml version="1.0" encoding="UTF-8"?><c3p0-config><!--c3p0的缺省（默認(rèn)）配置如果在代碼中"ComboPooledDataSource ds=new ComboPooledDataSource();"這樣寫就表示使用的是c3p0的缺省（默認(rèn)）--><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property><property name="user">root</property><property name="password">123456</property><property name="acquiredIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">20</property></default-config><!--c3p0的命名配置如果在代碼中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"這樣寫就表示使用的是mysql的缺省（默認(rèn)）--><named-config name="MySQL"><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property><property name="user">root</property><property name="password">123456</property><property name="acquiredIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">20</property></named-config></c3p0-config>

Demo

xml文件默認(rèn)能讀取到

public static Connection connection() throws Exception {ComboPooledDataSource dataSource=new ComboPooledDataSource();return dataSource.getConnection();
}

自帶日志