天天看點
傳回

java連接配接MySQL資料庫使用jdbc增删改查

java連接配接MySQL資料庫代碼↓

jdbc

資料庫裡的user表

java連接配接MySQL資料庫使用jdbc增删改查

把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);
	}

}

古之成大事者，必有通世之才能，亦必有堅韌不拔之志。

增删改查 資料庫 mysql java JDBC
上一篇: JAVA實作對MySQL資料庫的增删改查
下一篇: JDBC的增删改查（mysql資料庫）

繼續閱讀