华为GaussDB：封装JDBC数据连接访问华为GaussDB数据库实现CRUD

1.创建数据库表和索引（数据库操作工具使用的是华为Data Studio）

设置数据库主键列：

 

创建系列，用于生成唯一主键ID：

select dbuser.user_seq.nextval from sys_dummy；

 

2.封装高斯数据库JDBC访问类

• 下载 GaussDB 数据库 JDBC 驱动 jar 包：

JDBC包名： com.huawei.gauss.jdbc.ZenithDriver.jar

下载地址1：https://support.huaweicloud.com/devg-rds/rds_gaussdb_0008.html

下载地址2：https://download.youkuaiyun.com/download/nosprings/12139596

注意下载后解压，然后找如下jar文件：

package com.chinasofti.gaussdb.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
	private String url = "jdbc:zenith:@121.36.0.244:1888"; // 连接字符串
	private String dbUserName = "dbuser";                  // 用户名
	private String dbUserPwd = "User@123";                 // 密码
	protected Connection conn = null;

	public Connection getConnection() {
		if (conn != null)
			return conn;
		
		try {
			// 加载驱动类
			Class.forName("com.huawei.gauss.jdbc.ZenithDriver");
			// 获取数据库连接
			conn = DriverManager.getConnection(url, dbUserName, dbUserPwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 封装底层数据库连接关闭方法
	 * @author liuhaibing
	 * @date 2019年12月18日 
	 * @version 1.0
	 */

	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭数据库的自动事务提交功能，从自己控制事务的提交或者回滚
	 * @author liuhaibing
	 * @date 2019年12月18日
	 * @version 1.0
	 */

	public void openTransaction() {
		if (conn != null) {
			try {
				// 关闭数据库操作的自动commit功能
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 手动提交事务 
	 * @author liuhaibing
	 * @date 2019年12月18日 
	 * @version 1.0
	 */
	public void commit() {
		if (conn != null) {
			try {
				conn.commit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

	}

	public void rollback() {
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String [] args) {
		new BaseDao().getConnection();
	}

}

   

3. 实现对数据库表的 CRUD

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

public class UserDao extends BaseDao {
	
	/**
	 * 创建用户
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public int createUser(User user) throws SQLException {
		Connection conn = this.getConnection();
		int result = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("insert into t_users "
					+ "(user_id, user_name, user_pwd) values(dbuser.user_seq.nextval, ?, ?)");
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getUserPwd());	
			result = ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				ps.close();
			}
		}
		
		return result;
		
	}
	
	/**
	 * 删除用户
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public int removeUser(User user) throws SQLException {
		Connection conn = this.getConnection();
		int result = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("delete from t_users where user_id=?");
			ps.setInt(1, user.getUserId());
			result = ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				ps.close();
			}
		}
		return  result;
	}
	
	/**
	 * 修改用户
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public int updateUser(User user) throws SQLException {
		Connection conn = this.getConnection();
		int result = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("update t_users "
					+ "set user_pwd=? where user_id=?");
			ps.setString(1, user.getUserPwd());
			ps.setInt(2, user.getUserId());	
			result = ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				ps.close();
			}
		}
		return  result;
		
	}
	
	/**
	 * 查询用户
	 * @return
	 * @throws SQLException
	 */
	public List<User> findUserList() throws SQLException {
			
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();
		try {
			ps = conn.prepareStatement("select * from t_users ");
			rs = ps.executeQuery();
			User temp = null;
			while(rs.next()) {
				temp = new User();
				temp.setUserId(rs.getInt(1));
				temp.setUserName(rs.getString(2));
				temp.setUserPwd(rs.getString(3));
				userList.add(temp);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				ps.close();
			}
		}
		return userList;	
	}
	
}

4.测试结果

import java.sql.SQLException;
import java.util.List;

public class Test {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		UserDao userDao = new UserDao();
		User user = new User();
		user.setUserName("zhaoliu1");
		user.setUserPwd("123456");
		
		try {
			// 创建用户
			int result = userDao.createUser(user);
			
			if(result > 0) {
				System.out.println("写入成功");
			}
			
			// 查询用户
			
		    List<User> userList = userDao.findUserList();
		    
		    for(User temp : userList) {
		    	System.out.print(temp.getUserName() + "\t");
		    	System.out.println(temp.getUserPwd());
		    }
			
			// 修改密码
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			userDao.closeConnection();
		}
	}

}