源码仓库:https://gitee.com/DerekAndroid/jdbctest.git
用PreparedStatement实现增删改查操作
准备工作sql语句:
###创建分类表
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT ,
cname VARCHAR(100)
);
INSERT INTO category (cid,cname) VALUES('1','agen');
INSERT INTO category (cid,cname) VALUES('2','nice');
INSERT INTO category (cid,cname) VALUES('3','good');
###创建用户表
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT ,
username VARCHAR(100),
PASSWORD VARCHAR(100)
);
INSERT INTO users (username,PASSWORD) VALUES('agen','123');
jdbc工具类:
package demo06;
import java.sql.*;
public class JDBCUtils {
//这个工具类,主要为我们获取一个数据库连接
private static String driverName = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/day04";
private static String username = "root";
private static String password = "123";
//静态代码块,目的,让第一次使用到JDBCUtils中加载驱动,第二次以后不再加载了
static{
//1.加载驱动
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
//System.out.println("驱动加载失败..请检查驱动包");
throw new RuntimeException("驱动加载失败..请检查驱动包");
}
}
public static Connection getConnection() throws Exception{
//2.获取和数据库的连接
Connection conn = DriverManager.getConnection(url, username, password);
//3.返回连接对象
return conn;
}
//关闭所有资源的统一代码
public static void closeAll(Connection conn,Statement st,ResultSet rs){
//负责关闭
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package demo06;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 用PreparedStatement实现增删改查操作
*
* @author yingpeng
*
*/
public class TestDemo {
public static void main(String[] args) {
// insert();
// delete();
// update();
query();
}
//插入
public static void insert(){
//获取三个对象
Connection conn = null;
PreparedStatement pst = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
///2.创建SQL预处理对象(PreparedStatement)
String sql = "insert into users (username,password) values(?,?)";
pst = conn.prepareStatement(sql);
//3.给pst中的sql?号占位符赋值
pst.setObject(1, "前妻");
pst.setObject(2, "3838");
//4.执行语句
int rows = pst.executeUpdate();
System.out.println(rows);
} catch (Exception e) {
System.out.println(e);
}finally {
JDBCUtils.closeAll(conn, pst, null);;
}
}
//删除
public static void delete(){
//获取三个对象
Connection conn = null;
PreparedStatement pst = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.获取 预处理对象
String sql = "delete from users where uid = ?";
pst = conn.prepareStatement(sql);
//3.给pst中的sql设置值
pst.setInt(1, 5);
//4.执行
int rows = pst.executeUpdate();
System.out.println(rows);
} catch (Exception e) {
// TODO: handle exception
}finally {
JDBCUtils.closeAll(conn, pst, null);
}
}
//修改
public static void update(){
//获取三个对象
Connection conn = null;
PreparedStatement pst = null;
try {
//1.获取连接
conn = JDBCUtils.getConnection();
//2.获取 预处理对象
String sql = "update users set password = ? where uid = ?";
pst = conn.prepareStatement(sql);
//3.设置值
pst.setObject(1, "111");
pst.setObject(2, "2");
//4.执行
int rows = pst.executeUpdate();
System.out.println(rows);
} catch (Exception e) {
// TODO: handle exception
}finally {
JDBCUtils.closeAll(conn, pst, null);
}
}
//查询
public static void query(){
//获取三个对象
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from users where username like ?";
pst = conn.prepareStatement(sql);
pst.setObject(1, "%l%");
rs = pst.executeQuery();
//处理结果集
while(rs.next()){
Object uid = rs.getObject("uid");
Object username = rs.getObject("username");
Object password = rs.getObject("password");
System.out.println(uid+"\t"+username+"\t"+password);
}
} catch (Exception e) {
// TODO: handle exception
}finally {
JDBCUtils.closeAll(conn, pst, rs);
}
}
}