mysql 安装教程、使用 & 链接eclipsel增删改查

安装

建用户建库建表

插入数据

创建一个用户后
打开cmd，cd进入mysql安装路径下的bin文件夹，然后:
mysql -u 用户名 -p回车或mysql -u 用户名 -p 数据库名 回车(不要分号)
输入密码:*******

______________________________________________

授权
grant all privileges on 指定数据库.* to ‘username’@’%’;

在操作 mysql 库做用户的增删修改的时候，操作完毕的时候最好使用 flush privilege 命令刷新一下权限。否则可能会修改不生效。

java web页面可能出现的错误：

java.sql.SQLException: The server time zone value ‘?й???’

解决方法：在连接数据库的url后增加serverTimezone=UTC字段

dbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC

or 其他的写法如：

private static final String URI = "jdbc:mysql://localhost:3306/数据库名字?"

			+ "user=用户名&password=密码&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";

错误：
Loading class `com.mysql.jdbc.Driver’
解决方法：更改jdbc.properties配置 .com.mysql.jdbc.Driver 改为 com.mysql.cj.jdbc.Driver

______________________________________________

Eclipse项目

mysql 用户名: root 密码: 123456
新建一个数据库为 database1

以下为此项目（eclipse连接mysql进行增删改查）修正后代码

DBUtil.java

package com.imooc.jdbc;

 

import java.sql.Connection;

import java.sql.DriverManager;

 

public class DBUtil {

 
	
	private static final String URI = "jdbc:mysql://localhost:3306/database1?"

			+ "user=root&password=123456&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";

	

	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(URI);

		

		return conn;

	}

	

}

UserVO.java

package com.imooc.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 + "]";

	}

 

}

UserDao.java

查询所有的数据，在UserDao中定义一个queryAll()方法：

package com.imooc.jdbc;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import java.sql.Statement;



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 static void main(String[] args) {

		UserDao dao = new UserDao();

		

		try {

			List<UserVO> userList = dao.queryAll();

			for(UserVO user : userList) {

				System.out.println(user);

			}

		} catch (Exception e) {

			e.printStackTrace();

		}

	}
	
	
	


}

运行结果

根据条件查询，定义一个queryByParams方法：

package com.imooc.jdbc;
import java.util.HashMap;

import java.util.Map;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;
public class UserDao {



	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 static void main(String[] args) {

		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中写一个addUser方法用于新增一条信息：

package com.imooc.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;





public class UserDao {



	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 static void main(String[] args) {

		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();

		}

	}



	


}

运行前：

运行成功后：

接下来再写一个删除的方法，根据用户的id来删除数据：

package com.imooc.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;





public class UserDao {



	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 static void main(String[] args) {

		UserDao dao = new UserDao();

		

		try {

			dao.deleteUser(7);

		} catch (Exception e) {

			e.printStackTrace();

		}

	}



	


}

运行成功后：

最后来看一下更新数据库：

package com.imooc.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;





public class UserDao {



	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();

		}

	}

	


}

运行成功后：