JDBC实战教程(三)-操作数据库实现简单的增删改查
在前面的文章中讲述了如何配置jdbc信息、加载驱动的方式及如何获取Connection连接，下面来介绍下jdbc的基本操作CRUD如下：
一、首先我们需要建一个名为Dog的类如下：
package com.zhq.jdbc;
public class Dog {
// 🐶名字
private String name;
// 拥有者👨
private String ower;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getOwer() {
return ower;
}
public void setOwer(String ower) {
this.ower = ower;
}
}
二、然后在JDBCUtil类中定义四个方法分别为insert(),delete(),update(),select()如下：
// 增加数据
public static int insert(Dog dog) {
String sql = "insert into dog(name,ower) values(?,?)";
Connection con = getConnection();
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
//设置第一个占位符的值
ps.setString(1, dog.getName());
//设置第二个占位符的值
ps.setString(2, dog.getOwer());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con, ps);
}
return 0;
}
//更新记录
public static int update(Dog dog) {
Connection conn = getConnection();
String sql = "update dog set name= ? where ower = ?";
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
//设置第一个占位符的值
pstmt.setString(1, dog.getName());
//设置第二个占位符的值
pstmt.setString(2, dog.getOwer());
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn,pstmt);
}
return 0;
}
//删除记录
public static int delete(Dog dog) {
Connection con = getConnection();
String sql = "delete from dog where name=? and ower=?";
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
//设置第一个占位符的值
ps.setString(1, dog.getName());
//设置第二个占位符的值
ps.setString(2, dog.getOwer());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con,ps);
}
return 0;
}
//查询记录
public static List<Dog> select(String ower){
Connection con = getConnection();
String sql = "select * from dog where ower=?";
PreparedStatement ps = null;
ResultSet rs = null;
List<Dog> dogs = new ArrayList<>();
try {
ps = con.prepareStatement(sql);
//设置第一个占位符的值
ps.setString(1, ower);
rs = ps.executeQuery();
while(rs.next()){
Dog dog = new Dog();
dog.setName(rs.getString(1));
dog.setOwer(rs.getString(2));
dogs.add(dog);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con,ps,rs);
}
return dogs;
}
三、定义一个测试类来对上面的方法进行测试如下：
package com.zhq.jdbc;
import java.util.List;
public class TestJDBC {
public static void main(String[] args) {
Dog dog = new Dog();
dog.setName("小白");
dog.setOwer("张三");
int result = JDBCUtil.insert(dog);
if(result == 0){
System.out.println("插入数据成功");
} else {
System.out.println("插入数据失败");
}
//把张三的狗的名字改为大白菜
dog.setName("大白菜");
result = JDBCUtil.update(dog);
if(result == 0){
System.out.println("更新数据成功");
} else {
System.out.println("更新数据失败");
}
//查找属于李四的🐶
List<Dog> dogs = JDBCUtil.select("李四");
//删除张三的🐶
result = JDBCUtil.delete(dog);
}
}
附源码如下：
package com.zhq.jdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class JDBCUtil {
public static void main(String[] args) {
String sql = "select * from user";
doStatement(sql);
}
static {
try {
Class.forName(getProperties("jdbc.driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
String url = getProperties("url");
String user = getProperties("user");
String password = getProperties("password");
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static String getProperties(String key) {
Properties prop = new Properties();
try {
prop.load(new FileInputStream("jdbc.properties"));
return prop.getProperty(key);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
public static ResultSet doStatement(String sql) {
Connection con = getConnection();
try {
Statement stmt = con.createStatement();
return stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static ResultSet doPreparedStatement(String sql) {
Connection con = getConnection();
try {
PreparedStatement stmt = con.prepareStatement(sql);
return stmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection con, Statement st, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection con, Statement st) {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 增加数据
public static int insert(Dog dog) {
String sql = "insert into dog(name,ower) values(?,?)";
Connection con = getConnection();
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
//设置第一个占位符的值
ps.setString(1, dog.getName());
//设置第二个占位符的值
ps.setString(2, dog.getOwer());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con, ps);
}
return 0;
}
//更新记录
public static int update(Dog dog) {
Connection conn = getConnection();
String sql = "update dog set name= ? where ower = ?";
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
//设置第一个占位符的值
pstmt.setString(1, dog.getName());
//设置第二个占位符的值
pstmt.setString(2, dog.getOwer());
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn,pstmt);
}
return 0;
}
//删除记录
public static int delete(Dog dog) {
Connection con = getConnection();
String sql = "delete from dog where name=? and ower=?";
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
//设置第一个占位符的值
ps.setString(1, dog.getName());
//设置第二个占位符的值
ps.setString(2, dog.getOwer());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con,ps);
}
return 0;
}
//查询记录
public static List<Dog> select(String ower){
Connection con = getConnection();
String sql = "select * from dog where ower=?";
PreparedStatement ps = null;
ResultSet rs = null;
List<Dog> dogs = new ArrayList<>();
try {
ps = con.prepareStatement(sql);
//设置第一个占位符的值
ps.setString(1, ower);
rs = ps.executeQuery();
while(rs.next()){
Dog dog = new Dog();
dog.setName(rs.getString(1));
dog.setOwer(rs.getString(2));
dogs.add(dog);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(con,ps,rs);
}
return dogs;
}
}
