数据库连接类:
package util;
import java.sql.*;
/**
* TODO
*
* @author
* @date 2020/12/10 9:07
*/
public class DBUtil {
private final static String DRIVER = "com.mysql.jdbc.Driver"; //某个MySQL版本是com.mysql.cj.jdbc.Driver
private final static String URL = "jdbc:mysql://localhost:3306/db_test?CharacterEncoding=utf-8"; //db_test是数据库名
private final static String USERNAME = "root"; //用户名
private final static String PASSWORD = "27348915Ljh"; //密码
private static Connection conn = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
//加载驱动
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
*
* 获取连接
* @param
* @author
* @date 2020/12/10
*/
public static Connection getConn(){
try {
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
/**
*
* 关闭连接
* @param
* @author
* @date 2020/12/10
*/
public static void close(){
try {
if(rs != null) rs.close();
if (ps != null) ps.close();
if (conn != null) conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
/**
*
* 查询数据库
* @param sql
* @param objects
* @author
* @date 2020/12/10
*/
public static ResultSet executeQuery(String sql,Object... objects){
conn = getConn();
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
ps.setObject((i+1), objects[i]);
}
rs = ps.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
/**
*
* 数据库增、删、改
* @param sql
* @param objects
* @author
* @date 2020/12/10
*/
public static int executeUpdate(String sql,Object... objects){
conn = getConn();
int result = 0;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
ps.setObject((i+1), objects[i]);
}
result = ps.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
close();
}
return result;
}
//测试数据库是否连接成功,写入项目时可注释
public static void main(String[] args) {
System.out.println(getConn());
//输出类似:com.mysql.jdbc.JDBC4Connection@2b71fc7e ,则连接成功
}
}
数据库操作
示例:
/**
* Products 是实体类
* 问号是需要替换的内容
* 根据商品Id查询商品是否存在
* @param productsId
* @author
* @date 2020/12/10
*/
public Products queryById(int productsId){
Products products = new Products();
String sql = "select * from products where id = ?"; //products 是数据库表
ResultSet rs = DBUtil.executeQuery(sql,productsId);
try {
if (rs.next()){
products.setId(rs.getInt("id"));
products.setName(rs.getString("name"));
products.setPrice(rs.getInt("price"));
products.setNums(rs.getInt("nums"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
DBUtil.close();
}
return products;
}
/**
* 增、删、改返回值类型是int ,值是数据库更新数据的条数
* 修改商品信息
* @param products
* @author
* @date 2020/12/10
*/
public boolean update(Products products){
String sql = "update products set nums = ? where id = ?";
return DBUtil.executeUpdate(sql, products.getNums(),products.getId()) > 0;
}