40-42
提取工具类
db.properties
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456
JdbcUtils.java
package com.lcr.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet rs)
{
if (rs!=null)
{
try{
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!=null)
{
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
插入测试
package com.lcr.lesson02;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获得SQL的执行对象
String sql ="INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES (4,'LJJC','123456','123456@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0)
{
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
删除测试
package com.lcr.lesson02;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获得SQL的执行对象
String sql = "DELETE FROM users WHERE id=4;";
int i = st.executeUpdate(sql);
if(i>0)
{
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
更新测试
package com.lcr.lesson02;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获得SQL的执行对象
String sql = "UPDATE users SET `NAME`='LJJC',`email`='123321@qq.com' WHERE id=1";
int i = st.executeUpdate(sql);
if(i>0)
{
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
查询测试
package com.lcr.lesson02;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql ="select * from users where id = 1";
rs = st.executeQuery(sql); //查询完毕会返回一个结果集
while (rs.next())
{
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
PreparedStatement对象
PreparedStatement可以防止SQL注入,效率更高。
PreparedStatement防止SQL注入的本质,把传递进来的参数当作字符
假设其中存在转义字符,比如‘’会被直接转义
插入测试
package com.lcr.lesson03;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)value(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动赋值
st.setInt(1,4);
st.setString(2,"kkk");
st.setString(3,"123123");
st.setString(4,"456456@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
删除测试
package com.lcr.lesson03;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "delete from users where id=?";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动赋值
st.setInt(1,4);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
更新测试
package com.lcr.lesson03;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "update users set `NAME`=? where id=? ;";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动赋值
st.setString(1,"变形");
st.setInt(2,1);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
查询测试
package com.lcr.lesson03;
import com.lcr.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "select * from users where id=?";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动赋值
st.setInt(1,1);
//执行
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}