java连接MySQL数据库代码↓
jdbc
数据库里的user表
把user表进行实体化
package entity;
import java.io.Serializable;
/**
* @descibe User用户表
* @author xiaojian
* @date 2020年7月7日
*/
public class User implements Serializable {
//实现Serializable进行序列化操作
private static final long serialVersionUID = 7247119888988667135L; // 序列化
private Integer id; // 用户id
private String user_name; // 用户名
private String user_pwd; // 用户密码
// 构造方法
public User() {
super();
}
public User(String user_name, String user_pwd) {
super();
this.user_name = user_name;
this.user_pwd = user_pwd;
}
public User(Integer id, String user_name, String user_pwd) {
super();
this.id = id;
this.user_name = user_name;
this.user_pwd = user_pwd;
}
// get、set方法
/**
* @return the id
*/
public Integer getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(Integer id) {
this.id = id;
}
/**
* @return the user_name
*/
public String getUser_name() {
return user_name;
}
/**
* @param user_name the user_name to set
*/
public void setUser_name(String user_name) {
this.user_name = user_name;
}
/**
* @return the user_pwd
*/
public String getUser_pwd() {
return user_pwd;
}
/**
* @param user_pwd the user_pwd to set
*/
public void setUser_pwd(String user_pwd) {
this.user_pwd = user_pwd;
}
@Override
public String toString() {
return "User [id=" + id + ", user_name=" + user_name + ", user_pwd=" + user_pwd + "]";
}
}
添加用户
public class UserDao {
private Connection conn = null; // 连接
private PreparedStatement ps = null; // 预编译
private ResultSet rs = null; // 查询
public int addUser(User user) {
// 定义SQL语句
String sql = "insert into `user` (`user_name`,`user_pwd`) values (?,?)";
int n = 0; //数据库返回是否插入完成
try {
// 获得连接
conn = jdbcUtils.getConnection();
// 预编译SQL语句
ps = conn.prepareStatement(sql);
// 获取user对象里面的值
ps.setString(1, user.getUser_name());
ps.setString(2, user.getUser_pwd());
n = ps.executeUpdate();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
} finally {
// 释放资源
jdbcUtils.close(conn, ps);
}
return n;
}
}
//数量少可以用拼接的方式
public int addUser(User user) {
// 定义要执行的SQL语句
String sql = "INSERT INTO tb_user (username,`password`) VALUES ('" + user.getUserName() + "',MD5('"
+ user.getPassword() + "'))";
// 设置
int n = 0;
try {
// 通过Jian类中的getConnection方法获得与数据库的连接
conn = Jian.getConnection();
// 预编译执行上面的SQL语句
ps = conn.prepareStatement(sql);
// 执行SQL
n = ps.executeUpdate();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
} finally {
Jian.close(conn, ps);
}
return n;
}
删除用户
//接UserDao类
public int delUser(int uid) {
// 定义要执行的SQL语句
String sql = "DELETE FROM tb_user WHERE uid = " + uid;
int n = 0;
try {
conn = Jian.getConnection();
ps = conn.prepareStatement(sql);
n = ps.executeUpdate();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
} finally {
Jian.close(conn, ps);
}
return n;
}
修改信息
public int upUser(User user) {
String sql = "update tb_user set password = " + user.getPassword() + ", status = " + user.getStatus()
+ " where uid = " + user.getUid();
int z = 0;
try {
conn = Jian.getConnection();
ps = conn.prepareStatement(sql);
z = ps.executeUpdate();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
} finally {
Jian.close(conn, ps);
}
return z;
}
查询所有
public List<User> getAllUser() {
String sql = "select * from tb_user";
//接收从数据库反馈的数据用集合装起来
List<User> list = new ArrayList<>();
try {
conn = Jian.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int uid = rs.getInt("uid");
String sname = rs.getString("username");
String pwd = rs.getString("password");
int sta = rs.getInt("status");
Date regtime = rs.getDate("regtime");
// 实例化一个User对象,并添加到list集合
list.add(new User(uid, sname, pwd, sta, regtime));
}
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
} finally {
Jian.close(conn, ps, rs);
}
return list;
}
查询单个
public User getUserById(int uid) {
String sql = "select * from tb_user where uid = " + uid;
User user = null;
try {
conn = Jian.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
user = new User();
user.setUid(uid);
user.setUserName(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setStatus(rs.getInt("status"));
user.setRegtime(rs.getDate("regtime"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
Jian.close(conn, ps, rs);
}
return user;
}
测试
package two;
import java.util.List;
import entity.User;
/**
* @descibe
* @author xiaojian
* @date 2020年7月2日
*/
public class Test {
public static void main(String[] args) {
// 添加
User user = new User();
user.setUserName("奎因");
user.setPassword("996");
// 把变量值赋给n,1代表插入成功,0代表失败
int n = new UserDao().addUser(user);
System.out.println(n);
// 删除id为2的用户信息
int m = new UserDao().delUser(2);
System.out.println(m);
// 修改id为1的用户信息
User us = new User();
us.setPassword("666666");
us.setStatus(0);
us.setUid(1);
int z = new UserDao().upUser(us);
System.out.println(z);
// 得到数据库里所有的用户
List<User> allUser = new UserDao().getAllUser();
for (User u : allUser) {
System.out.println(u);
}
// 得到id为3的用户信息
User u = new UserDao().getUserById(3);
System.out.println(u);
}
}
古之成大事者,必有通世之才能,亦必有坚韧不拔之志。