用Java操作資料庫時
加載驅動Class.forName("com.mysql.cj.jdbc.Driver");
連接配接資料庫 DriverManager.getConnection(url, username, password);
獲得執行sql的對象connection.createStatement();
獲得傳回的結果集resultSet = statement.execute(sql);
關閉資料庫連接配接，釋放資源
import java.sql.*;
public class jdbctest{
public static void main(String[] args) throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
String username="root";
String password="123456";
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "SELECT `subjectno`,`subjectname` FROM `subject` WHERE `subjectno` <10";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("subjectno"+" subjectname");
while (resultSet.next()){
//System.out.println("subjectno"+" subjectname");
System.out.println(" "+resultSet.getObject("subjectno")+" "+resultSet.getObject("subjectname"));
//System.out.println("=====================");
}
resultSet.close();
statement.close();
connection.close();
}
複制代碼
注：這是mysql 8.0以上版本的操作，com.mysql.cj.jdbc.Driver和url的serverTimezone=Asia/Shanghai，與早期版本的有些差別
對象的解釋
Class.forName("com.mysql.cj.jdbc.Driver");//加載驅動這是8.0版本後的驅動
String url="jdbc:mysql://localhost:3306/school?"+
"useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
String username="root";
String password="123456";
複制代碼
url格式jdbc:mysql://localhost:3306/資料庫名?/參數1&參數2/serverTimezone=Asia/Shanghai
8.0版本的時區要特别注意一下
Connection connection = DriverManager.getConnection(url, username, password);
複制代碼
Connection就代表資料庫，常見的操作有
connection.commit();//事務送出
connection.rollback(); //事務復原
connection.setAutoCommit();//事務自動送出
Statement PrepareStatement是具體的執行sql的對象
String sql = "SELECT subjectno,subjectname FROM subject WHERE subjectno <10";
statement.execute(sql);//執行任何sql語句
int num = statement.executeUpdate(sql);//執行更新、插入、删除的sql，傳回受影響的行數(int)
statement.executeQuery(sql);//執行查詢的sql語句
ResultSet 傳回查詢的結果，封裝了所有結果集
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
//columnLabel=資料庫表中的列名
resultSet.getObject(columnLabel);
resultSet.getString(columnLabel);
resultSet.getInt(columnLabel);
resultSet.getFloat(columnLabel);
resultSet.getDate(columnLabel);
}
複制代碼
釋放資源
resultSet.close();
statement.close();
connection.close();
将連接配接資料庫的操作進行封裝
先了解一下如何擷取配置檔案.properties
先建立配置檔案db.properties
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
username=root
driver=com.mysql.cj.jdbc.Driver
password=123456
複制代碼
然後建立Utils
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils{
private static String url=null;
private static String username=null;
private static String password=null;
private static String driver=null;
static {
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//類加載器
Properties properties = new Properties();
properties.load(in);
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
Class.forName(driver);//驅動隻要啟動一次
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url,username,password);//擷取連接配接(資料庫)
}
//釋放資源
public static void release(Connection connection , Statement statement , ResultSet resultSet){
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (connection!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
複制代碼
然後進行增删改查
import utils.JdbcUtils;
import java.sql.*;
public class jdbctest{
public static void main(String[] args) throws ClassNotFoundException, SQLException{
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT `subjectno`,`subjectname` FROM `subject` WHERE `subjectno` <10";
resultSet = statement.executeQuery(sql);//擷取結果
System.out.println("subjectno" + " subjectname");
while (resultSet.next()) {
System.out.println(" " + resultSet.getObject("subjectno") + " " + resultSet.getObject("subjectname"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
複制代碼
防止SQL注入
使用PreparedStatement
在sql語句中用 ？ 表示變量
import utils.JdbcUtils;
import java.sql.*;
public class jdbctest{
public static void main(String[] args) throws ClassNotFoundException, SQLException{
Connection connection = null;
PreparedStatement statement = null;
//ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO subject (`subjectno`,`subjectname`,`classhour`,`gradeid`) VALUES(?,?,?,?)";
statement = connection.prepareStatement(sql);//預編譯SQL，不執行
statement.setInt(1,19);//第一個？
statement.setString(2,"實體");//第二個？
statement.setInt(3,700);
statement.setInt(4,1);
int row = statement.executeUpdate();
if (row >0)
{
System.out.println("插入成功");
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
JdbcUtils.release(connection,statement,null);
}
}
}
複制代碼
給第幾個？指派就用preparedStatement.setObject(?的位置,值),從1開始