1.创建数据库表和索引（数据库操作工具使用的是华为Data Studio）
设置数据库主键列： 创建系列，用于生成唯一主键ID：selectdbuser.user_seq.nextvalfromsys_dummy；
2.封装高斯数据库JDBC访问类
•下载GaussDB数据库JDBC驱动jar包：JDBC包名： com.huawei.gauss.jdbc.ZenithDriver.jar
下载地址1：/devg-rds/rds_gaussdb_0008.html
下载地址2：/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 12月18日 * @version 1.0*/public void closeConnection() {try {if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}/*** 关闭数据库的自动事务提交功能，从自己控制事务的提交或者回滚* @author liuhaibing* @date 12月18日* @version 1.0*/public void openTransaction() {if (conn != null) {try {// 关闭数据库操作的自动commit功能conn.setAutoCommit(false);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/*** 手动提交事务 * @author liuhaibing* @date 12月18日 * @version 1.0*/public void commit() {if (conn != null) {try {mit();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}public void rollback() {if (conn != null) {try {conn.rollback();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 stubUserDao 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 blocke.printStackTrace();}finally {userDao.closeConnection();}}}
