使用JDBC链接数据库,对数据库进行增删改查
程序员文章站
2022-05-06 20:53:40
...
一、常用类和接口
连接带数据库(Connection),建立指令操作符(statement、preparedStatement),执行查询指令(executeQuery),获得查询结果(ResultSet)等。
代码演示:
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.Properties;
/**
* 关于工具类,一般情况下定义的方法全是静态方法
* 不用创建对象,只加载一次,大大提高了效率
* 直接使用类名,静态方法名
* @author Administrator
*
*/
public class DBUtil {
public static Properties prop = new Properties();
static {
try {
prop.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static String CLASS_NAME = prop.getProperty("class_name");
public static String URL = prop.getProperty("url");
public static String USERNAME = prop.getProperty("username");
public static String PASSWORD = prop.getProperty("password");
/**
* 连接数据库的方法Connection
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(CLASS_NAME);
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
/**
* 封装获取Statement对象的方法
*/
public static Statement getStatement(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stmt;
}
/**
* 封装获取ResultSet对象的方法
*/
public static ResultSet getResultSet(Statement stmt,String sql) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/**
* 获取PreparedStatement对象
*/
public static PreparedStatement getPreparedStatement(Connection conn,String sql) {
PreparedStatement prep = null;
try {
prep = conn.prepareStatement(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return prep;
}
/**
* 关闭三个资源的方法
*/
public static void close(Connection conn,Statement stmt,ResultSet rs ) {
close(conn, stmt);
try {
if (rs != null) {
rs.close();
//rs = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(Connection conn,Statement stmt) {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* DML操作的静态方法(insert/update/delete)
* @param args
*/
public static int executeUpdate(Statement stmt,String sql) {
int recordsCount = 0;
try {
recordsCount = stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return recordsCount;
}
/*
* 封装一个回滚的方法
*
*/
public static void rollback(Connection conn) {
try {
conn.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
在上面的方法中,分别进行封装,得到了getconntion()、getStatement()、getPreparedStatement()、getResultSet()、close()、rollback()方法,同时建立了一个db.properties驱动文件,里面存放驱动类、url、用户名。密码和sql语句的信息。
db.properties文件
class_name=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/shop
username=root
password=root
三、我们进行对数据库中的数据进行增删改查。
建立一个user的类。
package com.tjise.dao;
import java.util.List;
import com.tjise.pojo.User;
/**
* 关于User实体类的相关操作
* @author Administrator
*
*/
public interface UserDao {
/**
* 查询所有数据的方法
*/
List<User> findAll();
/**
* 添加
*/
void add(User user);
/**
* 删除
*/
void delete(Integer id);
/**
* 修改
*/
void update(User user);
/**
* 根据id查询
*/
User findById(Integer id);
}
import java.sql.Timestamp;
/**
* 成员变量私有,提供对外公开的方法set/get
* @author Administrator
*/
public class User {
private Integer id;
private String username;
private String password;
private String phone;
private String addr;
private Timestamp rdate;//时间戳
public User() {
}
public User(Integer id, String username, String password, String phone, String addr, Timestamp rdate) {
this.id = id;
this.username = username;
this.password = password;
this.phone = phone;
this.addr = addr;
this.rdate = rdate;
}
public User(String username, String password, String phone, String addr) {
this.username = username;
this.password = password;
this.phone = phone;
this.addr = addr;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public Timestamp getRdate() {
return rdate;
}
public void setRdate(Timestamp rdate) {
this.rdate = rdate;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + ", phone=" + phone + ", addr="
+ addr + ", rdate=" + rdate + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((addr == null) ? 0 : addr.hashCode());
result = prime * result + ((id == null) ? 0 : id.hashCode());
result = prime * result + ((password == null) ? 0 : password.hashCode());
result = prime * result + ((phone == null) ? 0 : phone.hashCode());
result = prime * result + ((rdate == null) ? 0 : rdate.hashCode());
result = prime * result + ((username == null) ? 0 : username.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
User other = (User) obj;
if (addr == null) {
if (other.addr != null)
return false;
} else if (!addr.equals(other.addr))
return false;
if (id == null) {
if (other.id != null)
return false;
} else if (!id.equals(other.id))
return false;
if (password == null) {
if (other.password != null)
return false;
} else if (!password.equals(other.password))
return false;
if (phone == null) {
if (other.phone != null)
return false;
} else if (!phone.equals(other.phone))
return false;
if (rdate == null) {
if (other.rdate != null)
return false;
} else if (!rdate.equals(other.rdate))
return false;
if (username == null) {
if (other.username != null)
return false;
} else if (!username.equals(other.username))
return false;
return true;
}
}
Userdao.java
import java.util.List;
import com.tjise.pojo.User;
/**
* 关于User实体类的相关操作
* @author Administrator
*
*/
public interface UserDao {
/**
* 查询所有数据的方法
*/
List<User> findAll();
/**
* 添加
*/
void add(User user);
/**
* 删除
*/
void delete(Integer id);
/**
* 修改
*/
void update(User user);
/**
* 根据id查询
*/
User findById(Integer id);
}
UserDaoImpl.java
import java.sql.Connection;
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 com.tjise.dao.UserDao;
import com.tjise.pojo.User;
import com.tjise.util.DBUtil;
public class UserDaoImpl implements UserDao {
public static Connection conn = DBUtil.getConnection();
//MySQL数据库的数据访问
@Override
public List<User> findAll() {
Statement stmt = null;
ResultSet rs = null;
List<User> users = null;
String sql = "select * from t_user";
conn = DBUtil.getConnection();
stmt = DBUtil.getStatement(conn);
rs = DBUtil.getResultSet(stmt, sql);
users = new ArrayList<>();
try {
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setPhone(rs.getString("phone"));
user.setAddr(rs.getString("addr"));
user.setRdate(rs.getTimestamp("rdate"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(conn, stmt, rs);
}
return users;
}
@Override
public void add(User user) {
PreparedStatement prep= null;
String sql = "insert into t_user values(null,?,md5(?),?,?,now())";
prep = DBUtil.getPreparedStatement(conn, sql);
try {
prep.setObject(1, user.getUsername());
prep.setObject(2, user.getPassword());
prep.setObject(3, user.getPhone());
prep.setObject(4, user.getAddr());
prep.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(conn, prep);
}
}
@Override
public void delete(Integer id) {
PreparedStatement prep= null;
String sql = "delete from t_user where id=?";
prep = DBUtil.getPreparedStatement(conn, sql);
try {
prep.setObject(1, id);
prep.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(conn, prep);
}
}
@Override
public void update(User user) {
PreparedStatement prep= null;
String sql = "update t_user set username=?,password=?,phone=?,addr=? where id=?";
conn = DBUtil.getConnection();
prep = DBUtil.getPreparedStatement(conn, sql);
try {
prep.setObject(1, user.getUsername());
prep.setObject(2, user.getPassword());
prep.setObject(3, user.getPhone());
prep.setObject(4, user.getAddr());
prep.setObject(5, user.getId());
prep.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(conn, prep);
}
}
}
下一篇: Spring Boot多模块项目搭建介绍