java 原生Jdbc操作mysql数据库

java Jdbc读取mysql数据库

登录mysql官网下载驱动

https://dev.mysql.com/downloads/connector/j/

点第二个下载,如果要选择老版本 点选Archives 选择历史版本下载

打开项目,添加jar包

下载完成后解压缩到一个目录,右键该项目添加jar包

代码段

创建一个实体类

public class UserInfo {
	public String id;
	public String account;
	public String password;
	public String userName;
	public String phoneNumber;
	public String emalAddress;
	public Boolean enabled;
	
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return "id="+id+",account="+account+",userName="+userName+",password="+password+",enabled="+enabled+"";
	}
}

简单查询语句
public static void read() throws SQLException {
		// 连接字符串
		String connectionString = "jdbc:mysql://localhost:3306/javaproject";
		// 创建连接
		Connection connection = DriverManager.getConnection(connectionString, "root", "123qwe");
		// sql语句
		String sqlText = "select Id,Account,Password,UserName,Enabled,IsAdmin from rbac_User";
		// 执行查询
		Statement statement = connection.createStatement();
		ResultSet result = statement.executeQuery(sqlText);
		//可以获取结果集的信息
		//ResultSetMetaData metaData = result.getMetaData();
		// 读取数据
		while (result.next()) {
			//转换成对象
			UserInfo info = new UserInfo();
			info.id =result.getString(1);
			info.account  =result.getString(2);
			info.password  =result.getString(3);
			info.userName  =result.getString(4);
			info.enabled  =result.getBoolean(5);
			System.out.println(info);
		}
		//关闭连接
		connection.close();
	}

参数化sql语句
	// 参数化insert
	public static String insertUser(UserInfo userInfo) throws SQLException {
		// 连接字符串
		String connectionString = "jdbc:mysql://localhost:3306/javaproject";
		// 创建连接
		Connection connection = DriverManager.getConnection(connectionString, "root", "123qwe");
		// sql语句
		String sqlText = "insert into rbac_User(Id,account,password,userName,enabled,isAdmin,isDeleted,CreateTime) "
				+ "values(?,?,?,?,?,?,?,?)";
		// 预编译sql指令
		PreparedStatement statement = connection.prepareStatement(sqlText);
		// 添加sql参数
		String uuid = UUID.randomUUID().toString();
		statement.setString(1, uuid);
		statement.setString(2, userInfo.account);
		statement.setString(3, userInfo.password);
		statement.setString(4, userInfo.userName);
		statement.setBoolean(5, true);
		statement.setBoolean(6, true);
		statement.setBoolean(7, false);
		// 日期格式
		Date now = new Date(System.currentTimeMillis());
		statement.setDate(8, now);
		// 获取受影响行数
		int n = statement.executeUpdate();
		boolean success = n > 0;
		if (success) {
			System.out.println("添加成功!");
		} else {
			System.out.println("添加失败");
		}
		connection.close();
		return uuid;

	}

手动提交事务
public static void insertUserList(UserInfo[] userList) throws SQLException {
		// 连接字符串
		String connectionString = "jdbc:mysql://localhost:3306/javaproject";
		// 创建连接
		Connection connection = DriverManager.getConnection(connectionString, "root", "123qwe");
		//关闭自动提交事务
		connection.setAutoCommit(false);
		
		// sql语句
		String sqlText = "insert into rbac_User(Id,account,password,userName,enabled,isAdmin,isDeleted,CreateTime) "
				+ "values(?,?,?,?,?,?,?,?)";
		// 预编译sql指令
		PreparedStatement statement = connection.prepareStatement(sqlText);
		// 日期格式
		Date now = new Date(System.currentTimeMillis());
		int n = 0;
		try {
			for (UserInfo userInfo : userList) {

				// 添加sql参数
				String uuid = UUID.randomUUID().toString();
				statement.clearParameters();
				statement.setString(1, uuid);
				statement.setString(2, userInfo.account);
				statement.setString(3, userInfo.password);
				statement.setString(4, userInfo.userName);
				statement.setBoolean(5, true);
				statement.setBoolean(6, true);
				statement.setBoolean(7, false);
				statement.setDate(8, now);
				// 获取受影响行数
				n += statement.executeUpdate();
			}
			//提交更改
			connection.commit();
			System.out.println("总共" + n + "行数据添加成功");
		} catch (Exception e) {
			e.printStackTrace();
			//撤销更改
			connection.rollback();
			System.out.println("添加失败");
		}
		//关闭连接
		connection.close();

	}