实验4 JDBC访问数据库

实验链接：原实验的链接

关于连接数据库参考其他博主写的文章（不知道能不能看见我的收藏。。懒得放链接了）

把mysql和eclipse链接好之后，从管理员模式输入 mysql -u root -p
输入密码就可以使用了

首先我按照实验要求创建了一个表

然后插入数据的时候出现了问题

后来我又去搜索，参考其他博主写的把mysql.ini里面的utf8全改成了utf8mb4。。然而并没有用

于是我下载了nativecat（具体操作参考其他博主）

然后

不知道为什么命令插入的就是不行（可能是我太菜了，如有解决方法请告诉我！）

UserVO.java

package jdbc;

import java.util.Date;

public class UserVO {
	private int id;
	private String userName;
	private int age;
	private int sex;
	private Date createDt;
 
	public int getId() {
		return id;
	}
 
	public void setId(int id) {
		this.id = id;
	}
 
	public String getUserName() {
		return userName;
	}
 
	public void setUserName(String userName) {
		this.userName = userName;
	}
 
	public int getAge() {
		return age;
	}
 
	public void setAge(int age) {
		this.age = age;
	}
 
	public int getSex() {
		return sex;
	}
 
	public void setSex(int sex) {
		this.sex = sex;
	}
 
	public Date getCreateDt() {
		return createDt;
	}
 
	public void setCreateDt(Date createDt) {
		this.createDt = createDt;
	}
 
	@Override
	public String toString() {
		return "UserVO [id=" + id + ", userName=" + userName + ", age=" + age
				+ ", sex=" + sex + ", createDt=" + createDt + "]";
	}
 
}

DBUtil.java（为什么com.mysql.jdbc.Driver要改成com.mysql.cj.jdbc.Driver？）

package dao;


import java.sql.Connection;
import java.sql.DriverManager;

 
public class DBUtil {
 

	
	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	
	public static Connection connectDB() throws Exception {
		//1、加载数据库驱动
		Class.forName(DRIVER);
		//2、获取数据库连接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true","用户名","密码");
		
		return conn;
	}
	
}

UserDao.java

package dao;
import jdbc.UserVO;
import java.util.*;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.util.ArrayList;

public class UserDao {
	//查询
	public List<UserVO> queryAll() throws Exception {
		Connection conn = DBUtil.connectDB();
		String sql = "SELECT * FROM tbl_user_info";
		List<UserVO> userList = new ArrayList<UserVO>();
		
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()) {
			UserVO user = new UserVO();
			user.setId(rs.getInt("id"));
			user.setUserName(rs.getString("user_name"));
			user.setAge(rs.getInt("age"));
			user.setSex(rs.getInt("sex"));
			user.setCreateDt(rs.getDate("create_dt"));
			
			userList.add(user);
		}
		
		return userList;
	}
	
	
	//条件查询
	public List<UserVO> queryByParams(List<Map<String, Object>> params) throws Exception {
		Connection conn = DBUtil.connectDB();
		StringBuilder sql = new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1 ");
		
		for(Map<String, Object> param : params) {
			sql.append(" and ");
			sql.append(" " + param.get("col") + " ");
			sql.append(" " + param.get("rel") + " ");
			sql.append(" " + param.get("value") + " ");
		}
		System.out.println(sql.toString());
		
		List<UserVO> userList = new ArrayList<UserVO>();
		
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql.toString());
		while(rs.next()) {
			UserVO user = new UserVO();
			user.setId(rs.getInt("id"));
			user.setUserName(rs.getString("user_name"));
			user.setAge(rs.getInt("age"));
			user.setSex(rs.getInt("sex"));
			user.setCreateDt(rs.getDate("create_dt"));
			
			userList.add(user);
		}
		
		return userList;
	}
	
	
	//删除
	public void deleteUser(int id) throws Exception {
		Connection conn = DBUtil.connectDB();
		String sql = "DELETE FROM tbl_user_info WHERE id = ?";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, id);
		
		pstmt.execute();
	}
	
	
	
	
	//增加
	public void addUser(UserVO user) throws Exception {
		Connection conn = DBUtil.connectDB();
		String sql = "INSERT INTO tbl_user_info(user_name, age, sex, create_dt) "
				+ " VALUES(?, ?, ?, ?)";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, user.getUserName());
		pstmt.setInt(2, user.getAge());
		pstmt.setInt(3, user.getSex());
		pstmt.setDate(4,  new Date(new java.util.Date().getTime()));
		
		pstmt.execute();
	}
	
	
	//更新
	public void updateUser(UserVO user) throws Exception {
		Connection conn = DBUtil.connectDB();
		String sql = "UPDATE tbl_user_info SET user_name=?, age=?, sex=?"
				+ " WHERE id=?";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, user.getUserName());
		pstmt.setInt(2, user.getAge());
		pstmt.setInt(3, user.getSex());
		pstmt.setInt(4, user.getId());
		
		pstmt.executeUpdate();
	}
	public static void main(String[] args) {//主函数根据需要修改
		UserDao dao = new UserDao();//更新
		UserVO user = new UserVO();
		
		user.setUserName("Mary");
		user.setAge(30);
		user.setSex(0);
		user.setId(4);
		
		try {
			dao.updateUser(user);
		} catch (Exception e) {
			e.printStackTrace();
		}
		/*查询
		 *  UserDao dao = new UserDao();
		
		try {
			List<UserVO> userList = dao.queryAll();
			for(UserVO user : userList) {
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}*/
		
		
		
		
		
		/*条件查询
		 * UserDao dao = new UserDao();
		
		List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();
		Map<String, Object> param1 = new HashMap<String, Object>();
		param1.put("col", "user_name");
		param1.put("rel", "like");
		param1.put("value", "'%John%'");
		params.add(param1);
		
		Map<String, Object> param2 = new HashMap<String, Object>();
		param2.put("col", "sex");
		param2.put("rel", "=");
		param2.put("value", 1);
		params.add(param2);
		try {
			List<UserVO> userList = dao.queryByParams(params);
			for(UserVO user : userList) {
				System.out.println(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
 */
		
		
		
		
		
		/* 增加
		 * UserDao dao = new UserDao();
		UserVO user = new UserVO();
		
		user.setUserName("Tom");
		user.setAge(20);
		user.setSex(1);
		try {
			dao.addUser(user);
		} catch (Exception e) {
			e.printStackTrace();
		}
*/
		
		
		
		
		
		
		/* 删除
		 * UserDao dao = new UserDao();
		
		try {
			dao.deleteUser(7);
		} catch (Exception e) {
			e.printStackTrace();
		}*/
	}
}

最后说几句。。这个实验真的是差点把我弄疯。。。。。