一、基本操作
//3.获取连接
con = DriverManager.getConnection(url, user, password);
//4.预编译sql语句,返回PreparedStatement的实例
String sql = "insert into customers(name,email,birth)value(?,?,?)";//?:占位符
ps = con.prepareStatement(sql);
//5.填充占位符
ps.setString(1, "娜扎");
ps.setString(2, "nazha@qq.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("2000-01-01");
ps.setDate(3, new java.sql.Date(parse.getTime()));
//6.执行操作
ps.execute();
//7.资源的关闭
ps.close();
con.close();
二、封装连接类
public class JDBCUtil {
/**
* 获取数据库的链接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
//1.读取配置文件中的四个基本信息
//1)将配置文件转换成字符流
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
//2)将资源加载
Properties pros = new Properties();
pros.load(is);
//3)分别接收四个基本信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driver = pros.getProperty("driver");
//2.加载驱动
Class.forName(driver);
//3.获取连接
Connection con = DriverManager.getConnection(url, user, password);
return con;
}
/**
* 关闭连接和Statement的操作
* @param con
* @param ps
*/
public static void closeResource(Connection con, PreparedStatement ps) {
//7.资源的关闭
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (con != null) {
con.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
三、数据表的修改
import com.zjds.util.JDBCUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class PreparedStatementUpdateTest {
@Test
public void testUpdate() throws Exception {
//1.获取连接
Connection conn = JDBCUtil.getConnection();
//2.预编译sql语句,返回PreparedStatement的实例
String sql = "update cistomers set name = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setString(1, "古力娜扎");
ps.setString(2, "18");
//4.执行
ps.execute();
//5.资源的关闭
JDBCUtil.closeResource(conn, ps);
}
}
抛异常
import com.zjds.util.JDBCUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class PreparedStatementUpdateTest {
@Test
public void testUpdate() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取连接
conn = JDBCUtil.getConnection();
//2.预编译sql语句,返回PreparedStatement的实例
String sql = "update cistomers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setString(1, "古力娜扎");
ps.setString(2, "18");
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.资源的关闭
JDBCUtil.closeResource(conn, ps);
}
}
}
四、通用的增删改操作
import com.zjds.util.JDBCUtil;
import jdk.nashorn.internal.scripts.JD;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class PreparedStatementUpdateTest {
//通用的增删改操作
public void update(String sql, Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取连接
conn = JDBCUtil.getConnection();
//2.预编译
ps = conn.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.资源关闭
JDBCUtil.closeResource(conn, ps);
}
}
@Test
public void testUpdate() {
String sql = "update `order` set order_name = ? where order_id = ?";
update(sql, "DD", "2");
}
}
五、查询操作
import org.junit.jupiter.api.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class StuForQuery {
public Student queryForStu(String sql, Object ...args) throws Exception {
//1.获取连接
Connection conn = JDBCUtil.getConnection();
//2.预编译
PreparedStatement ps = conn.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4.获取结果集
ResultSet rs = ps.executeQuery();
// 1)获取结果集里的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 2)通过ResultMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
Student stu = new Student();
for (int i = 0; i < columnCount; i++) {
//获取列值
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
//给stu对象指定的columnName属性,赋值columnValue,通过反射
Field field = Student.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(stu, columnValue);
}
return stu;
}
JDBCUtil.closeResult(conn, ps, rs);
return null;
}
@Test
public void testQueryForStu() throws Exception {
String sql = "select * from Stu where id = ?";
Student Stu = queryForStu(sql, 1);
System.out.println(Stu);
}
}