一、Java連接mysql數(shù)據(jù)庫(kù)
1.1 流程
java連接mysql大致需要這六步：
-
導(dǎo)入驅(qū)動(dòng)包：這里我使用的是mysql-connector-java-8.0.17.jar(
點(diǎn)擊下載)，這個(gè)包連接mysql5.6，5.7，8.0版本都沒(méi)問(wèn)題。Class.forName("com.mysql.cj.jdbc.Driver"); -
url和賬戶名密碼
JDBC連接串：jdbc:mysql://<地址>:<端口>/<數(shù)據(jù)庫(kù)> -
獲取連接：
DriverManager.getConnection(url, user, password); -
執(zhí)行sql的Statement對(duì)象：
connection.createStatement(); - 執(zhí)行SQL
- 釋放連接
1.2 一個(gè)測(cè)試連接的java程序
JdbcFirstDemo.java
package com.peng.less01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.導(dǎo)入驅(qū)動(dòng)類
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.用戶信息和url
String url = "jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=false";
String user = "root";
String password = "12345678";
// 3.獲取連接
Connection conn = DriverManager.getConnection(url, user, password);
// 4.執(zhí)行SQL的對(duì)象
Statement sta = conn.createStatement();
// 5.執(zhí)行SQL
String sql = "select * from account";
ResultSet rs = sta.executeQuery(sql);
while (rs.next()){
System.out.println("id= " + rs.getObject("id"));
System.out.println("name= " + rs.getObject("name"));
System.out.println("money= " + rs.getObject("money"));
System.out.println("=========================================");
}
// 6.釋放連接
rs.close();
sta.close();
conn.close();
}
}
這里使用的是VScode，connector包放到了lib目錄下，代碼放在了src/com/peng/less01下。
執(zhí)行結(jié)果如下，顯示了shop.account表下面的三條記錄。
二、優(yōu)化：創(chuàng)建一個(gè)工具類
2.1 存在的問(wèn)題
1、JDBC連接串，用戶名，密碼等都存在于代碼中，需要進(jìn)行解耦
2、創(chuàng)建連接，釋放連接這些操作都是固定的，沒(méi)必要每次都重復(fù)寫(xiě)這些代碼（創(chuàng)建函數(shù)解決）
2.2 創(chuàng)建配置文件和工具類
src/db.properties 文件中記錄driver，url，user，password
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=false
user=root
password=12345678
src/com/peng/less02/utils/JdbcUtils.java 工具類：讀取properties配置文件
自動(dòng)讀取配置信息，加載mysql驅(qū)動(dòng)。
package com.peng.less02.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
// 這是一個(gè)工具類，用來(lái)減少重復(fù)操作
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
static{
try{
// 讀取db.properties
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 加載驅(qū)動(dòng)
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
// 創(chuàng)建連接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url,user,password);
}
// 釋放連接
public static void releaseConnection(Connection conn, Statement sta, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (sta != null){
try {
sta.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2.3 測(cè)試使用工具類進(jìn)行CRUD操作
測(cè)試插入操作
src/com/peng/less02/TestInsert.java
package com.peng.less02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.peng.less02.utils.JdbcUtils;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "insert into account(`id`,`name`,`money`) values(4,'藥水哥',4698888)";
int i = sta.executeUpdate(sql);
if (i > 0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, rs);
}
}
}
測(cè)試更新操作
src/com/peng/less02/TestUpdate.java
package com.peng.less02;
import java.sql.*;
import com.peng.less02.utils.JdbcUtils;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "update account set `money`=238888 where id=3";
int i = sta.executeUpdate(sql);
if (i > 0){
System.out.println("更新成功");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, null);
}
}
}
測(cè)試讀取操作
package com.peng.less02;
import java.sql.*;
import com.peng.less02.utils.JdbcUtils;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "select * from account";
rs = sta.executeQuery(sql);
while (rs.next()){
System.out.println("id= " + rs.getInt("id"));
System.out.println("name= " + rs.getString("name"));
System.out.println("money= " + rs.getObject("money"));
System.out.println("======================================================");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, rs);
}
}
}
三、SQL注入問(wèn)題
這里寫(xiě)一個(gè)登錄認(rèn)證程序，如果賬號(hào)，密碼輸入正確則允許登錄。為了測(cè)試，這里的允許登錄改為輸出賬號(hào)密碼。還使用前面寫(xiě)的JdbcUtils工具類
可以看到，這里我輸入的用戶名/密碼是：’ or '1=1 ??? ’ or '1=1
然后輸出了user表中的所有數(shù)據(jù)，這明顯是不合法的。存在sql注入的問(wèn)題
問(wèn)題就在于這段代碼，sql是拼接而成的。
String sql = "select * from user where `username`='" + username + "' and `password`='" + password + "'";
拼接的sql為：select * from user where `username`='' or '1=1' and `password`='' or '1=1';
3.1 SQL注入示例
SqlInjection.java
package com.peng.less02;
import com.peng.less02.utils.JdbcUtils;
import java.sql.*;
public class SqlInjection {
public static void main(String[] args) {
login("' or '1=1", "' or '1=1");
}
// 登錄功能
public static void login(String username, String password){
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
//這段代碼中sql是變量拼接而成的
String sql = "select * from user where `username`='" + username + "' and `password`='" + password + "'";
rs = sta.executeQuery(sql);
//輸入正確的賬號(hào)密碼就能夠登錄，這里為了測(cè)試就（輸入正確輸出賬號(hào)密碼）
while (rs.next()){
System.out.println("id= " + rs.getInt("id"));
System.out.println("username= " + rs.getString("username"));
System.out.println("password= " + rs.getObject("password"));
System.out.println("======================================================");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, rs);
}
}
}
四、使用PreparedStatement防止SQL注入
- 使用PreparedStatement，防止SQL注入，同時(shí)對(duì)于執(zhí)行多次的SQL更加高效
PreparedStatement，對(duì)sql進(jìn)行了預(yù)編譯，傳入的參數(shù)只會(huì)被當(dāng)作字符串來(lái)處理，而不會(huì)像前面對(duì)sql進(jìn)行了拼接。流程如下：
先編寫(xiě)sql --> 進(jìn)行預(yù)編譯 --> 增加sql參數(shù)值 --> 執(zhí)行sql
SqlInjection.java
package com.peng.less03;
import com.peng.less02.utils.JdbcUtils;
import java.sql.*;
public class SqlInjection {
public static void main(String[] args) {
// login("'' or 1=1", "'' or 1=1");
login("張三", "zhangsan");
}
// 登錄功能
public static void login(String username, String password){
Connection conn = null;
PreparedStatement psta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//這里使用 ? 占位符來(lái)代替
String sql = "select * from user where `username`=? and `password`=?";
//使用PreparedStatement，防止SQL注入，同時(shí)對(duì)于執(zhí)行多次的SQL更加高效
psta = conn.prepareStatement(sql);
psta.setString(1,username);
psta.setObject(2,password);
rs = psta.executeQuery();
//輸入正確的賬號(hào)密碼就能夠登錄，這里為了測(cè)試就（輸入正確輸出賬號(hào)密碼）
while (rs.next()){
System.out.println("id= " + rs.getInt("id"));
System.out.println("username= " + rs.getString("username"));
System.out.println("password= " + rs.getObject("password"));
System.out.println("======================================================");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, psta, rs);
}
}
}
五、JDBC操作事務(wù)
和用sql執(zhí)行事務(wù)類似，創(chuàng)建連接后setAutoCommit(false)，最后commit()，失敗則rollback()。
注意：rollback()部分可以不用寫(xiě)，事務(wù)失敗會(huì)自動(dòng)回滾。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-780751.html
TestTransaction.java文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-780751.html
package com.peng.less04;
import java.sql.*;
// 仍然使用前面寫(xiě)的JdbcUtils工具類
import com.peng.less02.utils.JdbcUtils;
public class TestTransaction {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
try {
conn = JdbcUtils.getConnection();
// 關(guān)閉自動(dòng)提交，開(kāi)啟事務(wù)
conn.setAutoCommit(false);
//模擬一個(gè)轉(zhuǎn)賬事務(wù)，A向大帥哥轉(zhuǎn)賬100塊
String sql1 = "update account set `money`=money - 100 where `name`='A'";
pst = conn.prepareStatement(sql1);
pst.executeUpdate();
// int x = 1/0;
String sql2 = "update account set `money`=money + 100 where `name`='大帥哥'";
conn.prepareStatement(sql2).executeUpdate();
conn.commit();
System.out.println("Success!");
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
// rollback()可以不用寫(xiě)，失敗的話會(huì)自動(dòng)回滾
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, pst, null);
}
}
}
到了這里，關(guān)于Java連接mysql數(shù)據(jù)庫(kù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
