JDBC连接数据库 - ToB企服应用市场:ToB评测及商务社交产业平台

//5版本
private static String driver = "com.mysql.jdbc.Driver";
//8版本
private static String driver = "com.mysql.cj.jdbc.Driver";

复制代码

private static String username = "root";

复制代码

private static String password = "123456";

复制代码

private static String url = "jdbc:mysql://localhost:3306/数据库名字?useSSL=false&characterEncoding=utf8&timeZone=UTC";

复制代码

Class.forName(driver);

复制代码

DriverManager.getConnection(url, username, password);

复制代码

private PreparedStatement pstm;
//?是要传入的数据，有几个写几个
String sql = "SELECT * FROM user WHERE id = ?;";
pstm = conn.prepareStatement(sql);

复制代码

//查询用这个
private ResultSet rs= null;
//如上面第三步需要一个int类型的数据id
pstm.setInt(1,id);
rs = pstm.executeQuery();
//其他的增删改,不需要返回结果集，他们返回一个整数，代表改变了几条数据
n = pstm.executeUpdate();

复制代码

//在查询的时候使用处理结果
//找下一个，通常写在while中
rs.next()
//获取
rs.getString()

复制代码

//这里我封装起来了省的来回写一大堆
//正着写，倒着关
//查询关三个
ResultSet.close;
PreparedStatement.close;
Connection.close;
//其他关两个
PreparedStatement.close;
Connection.close;

复制代码

import java.sql.*;
/**
* @Auther QY
* @Date 2023/11/16
*/
public class DBTools {
//(1)声明驱动
//我这里用的是8版本的
private static String driver = "com.mysql.cj.jdbc.Driver";
//(2)声明用户名
private static String username = "root";
//(3)声明密码
private static String password = "qy666";
//(4)数据库的完整地址
private static String url = "jdbc:mysql://localhost:3306/数据库名字?useSSL=false&characterEncoding=utf8&timeZone=UTC";
public static Connection ConnDB() {
Connection connection = null;
try {
//一、Class.forName()加载数据库连接驱动
Class.forName(driver);
//二、DriverManager.getConnection()获取数据连接对象
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
//六、释放资源
public static void closeDB(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
//(1)关闭连接对象
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//(2)关闭数据操作对象
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//(3)关闭结果集对象
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}

复制代码

public class UserDaoImpl implements UserDao {
//声明数据库连接对象
private Connection conn = DruidTools.getConn();
//声明数据库操作对象
private PreparedStatement pstm;
//声明结果集对象
private ResultSet rs= null;
/**
* 通过id号查一个用户
* @param id
* @return
*/
@Override
public User selectUserById(int id) {
//声明User
User user = null;
//写sql
String sql = "SELECT * FROM user WHERE id = ?;";
try {
pstm = conn.prepareStatement(sql);
//设置参数的值
pstm.setInt(1,id);
rs = pstm.executeQuery();
if (rs.next()){
user = new User();
String username = rs.getString("username");
String password = rs.getString("password");
user.setId(id);
user.setUsername(username);
user.setPassword(password);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//查不到返回null
return user;
}
/**
* 查询所有对象
* @return
*/
@Override
public List<User> selectUsers() {
List<User> users = new ArrayList<>();
//写sql语句
String sql = "select * FRom user;" ;
User user;
try {
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
while (rs.next()){
//跟实体类对应
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
user = new User();
user.setId(id);
user.setUsername(username);
user.setPassword(password);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return users;
}
/**
* 添加用户
* @param user
* @return
*/
@Override
public int insertUser(User user) {
//声明变量
int n = 0;
String sql = "INSERT INTO user(username,password,realname,gender,address,email,regdate) " +
"VALUES(?,?,?,?,?,?,?);";
try {
pstm = conn.prepareStatement(sql);
pstm.setString(1,user.getUsername());
pstm.setString(2,user.getPassword());
pstm.setString(3,user.getRealname());
pstm.setString(4,user.getGender());
pstm.setString(5,user.getAddress());
pstm.setString(6,user.getEmail());
pstm.setTimestamp(7,user.getRegdate());
//执行添加
n = pstm.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return n;
}
@Override
public Integer selectUserByUsername(String username) {
//声明对象
Integer temp = null;
//写sql
String sql = "SELECT id FROM user WHERE username = ?;";
//创建对象
try {
pstm = conn.prepareStatement(sql);
pstm.setString(1,username);
rs = pstm.executeQuery();
if (rs.next()){
temp = rs.getInt("id");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return temp;
}
/**
* 修改密码
* @param username
* @param newPassword
* @return
*/
@Override
public int updateUser(String username, String newPassword) {
int n = 0;
String sql = "UPDATE user SET password = ? WHERE username = ?;";
try {
pstm = conn.prepareStatement(sql);
pstm.setString(2,username);
pstm.setString(1,newPassword);
//执行修改
n = pstm.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return n;
}
@Override
public int deleteUser(String username) {
int n = 0;
String sql = "DELETE FROM user WHERE username = ?;";
try {
pstm = conn.prepareStatement(sql);
pstm.setString(1,username);
//执行修改
n = pstm.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return n;
}
}

复制代码