Java 通过jdbc4 连接SQL Server2008

た入场券 2022-07-14 14:54 229阅读 0赞

## eclipse ##

package com.hz.db;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * 数据库连接类
     * 
     * @author ztw
     * 
     *         2016-12-8
     */
    public class DbHelp {
        
    
        // com.microsoft.sqlserver.jdbc.SQLServerDriver.class
        // MonsterChatRoom
        private Connection conn = null;
        private Statement stmt = null;
        private PreparedStatement pstmt;
        private static String dbDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        private static String dbUri = "jdbc:sqlserver://localhost:1433;dataBaseName=MonsterChatRoom;";
        private static String dbUser = "sa";
        private static String dbPwd = "123456";
    
        public DbHelp() {
            getConnection();
        }
    
        public Connection getConnection() {
            try {
                Class.forName(dbDriver);
                conn = DriverManager.getConnection(dbUri, dbUser, dbPwd);
            } catch (ClassNotFoundException e) {
    
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (conn == null) {
                System.out.println("警告：数据库连接失败！");
            } else {
                System.out.println("数据库连接成功！");
            }
            return conn;
        }
    
        /**
         * 获取statement(发送命令)对象
         * 
         * @return
         */
        public Statement getStatement() {
            try {
                if (stmt == null) {
                    stmt = conn.createStatement();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return stmt;
        }
    
        /**
         * 预处理sql
         * 
         * @param sql
         * @return
         */
        public PreparedStatement getPreparedStatement(String sql) {
            try {
                pstmt = conn.prepareStatement(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return pstmt;
    
        }
    
        /**
         * 关闭数据库
         */
        public void close() {
            try {
                if (pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

> dbDriver=Referenced Libraries->sqljdbc4.jar->……jdbc->最后以……Driver.class结尾的路径！  
> ![这里写图片描述][SouthEast]  
> 把libs文件中的jdbc4->右键导入路径Build Path

### 增删改查 ###

package com.hz.util;
    
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.HashMap;
    
    import com.hz.bean.User;
    import com.hz.dao.DbOperate;
    import com.hz.db.DbHelp;
    
    /**
     * 用户数据库连接类
     * 
     * @author ztw
     * 
     *         2016-12-7
     */
    public class UserDatabaseUtil {
        
    
        // 表名
        private static final String T_USER = "T_User";
        // 表中的列名
        private static final String USER_ID = "user_id";
        private static final String USER_NAME = "user_name";
        private static final String USER_NICKNAME = "user_nickname";
        private static final String USER_PASSWORD = "user_password";
        private static final String USER_SEX = "user_sex";
        private static final String USER_ICON = "user_icon";
        private static final String USER_EMAIL = "user_email";
        private static final String USER_MONEY = "user_money";
        private static final String REGISTER_DATE = "register_date";
        private static final String LOG_AUTOGRAPH = "log_autograph";
    
        private DbHelp dbHelp;
    
        private Statement statement;
    
        private ResultSet rs;
    
        User user;
    
        public UserDatabaseUtil() {
            dbHelp = new DbHelp();
            statement = dbHelp.getStatement();
        }
    
        // 添加
        public boolean add(User user) {
    
            String sql = "insert into " + T_USER + "(" + USER_NAME + ","
                    + USER_PASSWORD + "," + USER_NICKNAME + "," + USER_SEX + ","
                    + USER_EMAIL + "," + LOG_AUTOGRAPH + ")"
                    + " values(?,?,?,?,?,?)";
            PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
            /**
             * 参数1：第几个问号 参数2：问号的值
             */
            try {
                pstmt.setString(1, user.getUser_name());
                pstmt.setString(2, user.getUser_password());
                pstmt.setString(3, user.getUser_nickname());
                pstmt.setString(4, user.getUser_sex());
                pstmt.setString(5, user.getUser_email());
                pstmt.setString(6, user.getLog_autograph());
                return pstmt.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }
    
        // 根据指定的信息查询数据
        public User selectName(String user_name) {
            try {
                String sql = "select * from " + T_USER + " where " + USER_NAME
                        + " = '" + user_name + "'";
                PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    user = new User(rs.getInt(1), rs.getString(2), rs.getString(3),
                            rs.getString(4), rs.getString(5), rs.getString(6),
                            rs.getString(7), rs.getDouble(8), rs.getString(9),
                            rs.getString(10));
                }
                return user;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
        // 根据指定的信息查询数据
        public User selectNickName(String NickName) {
            try {
                String sql = "select * from " + T_USER + " where " + USER_NICKNAME
                        + " = '" + NickName + "'";
                PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    user = new User(rs.getInt(1), rs.getString(2), rs.getString(3),
                            rs.getString(4), rs.getString(5), rs.getString(6),
                            rs.getString(7), rs.getDouble(8), rs.getString(9),
                            rs.getString(10));
                }
                return user;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    
        // 根据指定的信息查询数据
        public User selectName(User user_nickName) {
            String nickName = user_nickName+"";
            try {
                String sql = "select * from " + T_USER + " where " + USER_NICKNAME
                        + " = '" + nickName + "'";
                PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    user = new User(rs.getInt(1), rs.getString(2), rs.getString(3),
                            rs.getString(4), rs.getString(5), rs.getString(6),
                            rs.getString(7), rs.getDouble(8), rs.getString(9),
                            rs.getString(10));
                }
                return user;
    
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    
        // 修改信息
        public void update(String user_nickname, String user_sex, String user_icon,
                String user_email, String register_date, String log_autograph) {
    
            String sql = "update " + T_USER + " set " + USER_NICKNAME + "='"
                    + user_nickname + "' ," + USER_SEX + "='" + user_sex + "' ,"
                    + USER_ICON + "='" + user_icon + "' ," + USER_EMAIL + "='"
                    + user_email + "' ," + REGISTER_DATE + "='" + register_date
                    + "' ," + LOG_AUTOGRAPH + "='" + log_autograph + "'"
                    + " where " + USER_NICKNAME + " =  '" + user_nickname + "'";
    
            PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
    
            try {
                pstmt.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        // 修改
        public boolean update(String name,String pass) {
            String sql = "update " + T_USER + " set " + USER_PASSWORD
                    + "="+"'"+pass+"'"+" where " + USER_NAME + " = "+"'"+name+"'";
            PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
    
            try {
                pstmt.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }
    
        // 删除
        public boolean delete() {
    
            // delete dbo.T_User where _id = 2
            String sql = "delete " + T_USER + " where " + USER_ID + " = 2";
            PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
            try {
                return pstmt.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }
    
        // 查找全部
        public ResultSet getAll() {
            try {
                String sql = "select * from " + T_USER;
                PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
                rs = pstmt.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return rs;
        }
    }

需要什么就调用相应的方法！

[SouthEast]: /images/20220714/6299ed3a460646ecb6992e595a76d362.png