Java-JDBC-PreparedStatement进行CURD
- PreparedStatement 预编译语句对象可以方式SQL注入攻击,可以稍微提高SQL执行效率
-
- 注意:要使用预编译的时候,需要在url里面添加参数
useServerPrepStmts=true&cachePrepStmts=true
- 直接上代码PreparedStatement进行CURD
package com.shanlei.test03;
import com.shanlei.entity.Emp;
import javax.swing.text.html.HTMLDocument;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class TestCURD {
private static String url = "jdbc:mysql://localhost/mytestdb?useSSL=false&usrUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
private static String user = "XXXX";
private static String password = "XXXX";
private static String driver = "com.mysql.cj.jdbc.Driver";
public static void main(String[] args) {
testQuery();
}
public static void testAdd(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "insert into emp values (default,?,?,?,?,?,?,?);";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"john");
preparedStatement.setString(2,"MANAGER");
preparedStatement.setInt(3,7839);
preparedStatement.setDate(4,new Date(System.currentTimeMillis()));
preparedStatement.setDouble(5,3000.45);
preparedStatement.setDouble(6,0);
preparedStatement.setInt(7,30);
int result = preparedStatement.executeUpdate();
System.out.println("成功插入"+result+"条数据!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(null != preparedStatement){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != connection){
}try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void testUpdate(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "update emp set ename=?, job=? where empno=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"holmes");
preparedStatement.setString(2,"ANALYST");
preparedStatement.setInt(3,7935);
int result = preparedStatement.executeUpdate();
System.out.println("完成修改"+ result +"行数据");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(null != preparedStatement){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void testDelete(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "delete from emp where empno=?;";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 7935);
int result = preparedStatement.executeUpdate();
System.out.println("成功删除"+ result +"条数据");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(null!=preparedStatement){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void testQuery(){
Connection connection = null;
PreparedStatement preparedStatement = null;
List<Emp> list = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
String sql = "select * from emp where ename like ?;";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"%A%");
ResultSet resultSet = preparedStatement.executeQuery();
list = new ArrayList<Emp>();
while (resultSet.next()){
int empno = resultSet.getInt("empno");
String ename = resultSet.getString("ename");
String job = resultSet.getString("job");
int mgr = resultSet.getInt("mgr");
Date hiredate = resultSet.getDate("hiredate");
double sal = resultSet.getDouble("sal");
double comm = resultSet.getDouble("comm");
int deptno = resultSet.getInt("deptno");
list.add(new Emp(empno, ename, job, mgr, hiredate, sal, comm, deptno));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(null != preparedStatement){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
for (Emp emp : list) {
System.out.println(emp);
}
}
}