JDBC 配置连接

一、新建一个数据库配置文件，如：Config.properties

driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433; DatabaseName=ContactsBook2
username=sa
password=123456

二、SqlHelper.java

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;


public class SqlHelper {

    private  String DRIVER = "";//指定数据库驱动  
    private  String URL = "";//要连接的数据库  
    private  String USERNAME="";//用户名
    private  String PWD="";//密码

    private void init(){
        try {
            InputStream objsm=getClass().getResourceAsStream("Config.properties");
            Properties objpro=new Properties();
            objpro.load(objsm);
            DRIVER=objpro.getProperty("driver");
            URL=objpro.getProperty("url");
            USERNAME=objpro.getProperty("username");
            PWD=objpro.getProperty("password");
        } catch (Exception e) {
            e.getMessage();
        }
    }

    public SqlHelper() {
        init();
    }
    /** 
     * 获取连接 
     * @return   
     */  
    public Connection getConnection() {  
        try {  
            Class.forName(DRIVER);  

            Connection conn = DriverManager.getConnection(URL,USERNAME,PWD); 

            return conn;  
        } catch (SQLException e) {  
            e.getMessage();
        } catch (ClassNotFoundException e) { 
            e.getMessage();
        }  
        return null;  
    }  
    /** 
     * 关闭连接 
     * @param stat 
     * @param conn 
     */  
    public void close(Statement stat,Connection conn) {  
        try {  
            if(stat != null) {  
                stat.close();  
            }  
        } catch (SQLException e) {  
            e.toString();
        } finally {  
            try {  
                if(conn != null) {  
                    conn.close();  
                }  
            } catch (SQLException e) {  
                e.getMessage();
            }  
        }  
    }  

    public int ExecSql(String Sql) throws SQLException {
        Statement objSt = getConnection().createStatement();

        return objSt.executeUpdate(Sql);
    }

    public ResultSet ExecuteSqlByData(String Sql){
        ResultSet objRS = null;
        try
        {
            Statement objSt = getConnection().createStatement();

            objRS = objSt.executeQuery(Sql);
        }
        catch(SQLException e){
            e.getMessage();
        }
        catch(Exception e){
            e.getMessage();
        }

        return objRS;
    }


    public Object GetSingleValue(String Sql){
        ResultSet objRS = ExecuteSqlByData(Sql);
        Object _Temp = "";
        try {
            while(objRS.next()){
                _Temp= objRS.getObject(1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return _Temp;       
    }


     public ArrayList<Object[]> executeQuery(String sql){
            Connection conn = null;  
            PreparedStatement stat = null;  
            ResultSet rs = null; 
            ArrayList<Object[]> al=new ArrayList<Object[]>();
            try {
                conn = this.getConnection();
                stat = conn.prepareStatement(sql);

                rs=stat.executeQuery();

                ResultSetMetaData rsmd=rs.getMetaData();  
                int column=rsmd.getColumnCount();  
                while(rs.next()){  
                    Object[] ob=new Object[column];  
                    for(int i=0;i<ob.length;i++){  
                        ob[i]=rs.getObject(i+1);  
                    }  
                    al.add(ob);  
                }   
            } catch (SQLException e) {
                System.out.print(e.getMessage());
            }finally {  
                this.close(stat, conn);  
            } 
            return al;
        }

}

三、新建调用SqlHelper.java 的接口类：DB.java

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class DB {

public DB(){

    }

    public int Add(String Sql) throws SQLException{
        SqlHelper objSqlDB = new SqlHelper();
        return objSqlDB.ExecSql(Sql);
    }

    public ResultSet GetData(String Sql){
        SqlHelper objSqlDB = new SqlHelper();
        return objSqlDB.ExecuteSqlByData(Sql);      
    }

    public int Update(String sql) throws SQLException{
        SqlHelper objSqlDB = new SqlHelper();
        return objSqlDB.ExecSql(sql);

    }

    public int Delect(String sql) throws SQLException{
        SqlHelper objSqlDB = new SqlHelper();
        return objSqlDB.ExecSql(sql);
    }


    public Object GetSingleValue(String Sql){
        SqlHelper objSqlDB = new SqlHelper();
        return objSqlDB.GetSingleValue(Sql);        
    }

    //获取指定表的主键的编号 
    public String GetServilID(String TableName, String PkField){    
        String _Sql = "select CAST(ISNULL(MAX(substring(" + PkField + ",9, 6)), 0) as int) + 1 from " + TableName+ " where substring(" + PkField + ", 1, 8) = '" + GetDateString() + "' ";

        int _MaxID = Integer.parseInt(GetSingleValue(_Sql).toString());

        return GetDateString() + String.format("%06d", _MaxID);
    }

    public String GetDateString(){
        java.text.SimpleDateFormat objDateFormat = new java.text.SimpleDateFormat("yyyyMMdd");
         Date date = new Date();  
        return objDateFormat.format(date);
    }



}

四、UsersDao.java

package Dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import Bean.UserBean;

public class UsersDao extends DB {

    public UsersDao(){

        }

    /*
     * 验证系统登录(验证用户是否存在)
     * objBean: 用户实体
     */
    public boolean CheckUser(UserBean objBean){
        try{
            String _Sql = "select Count(*) from tb_UserLogin where userName ='" + objBean.getUserName() + "' and userPwd = '" + objBean.getUserPwd() + "' ";

            ResultSet objRS = GetData(_Sql);
            int _Result = 0;
            while(objRS.next()){
                _Result = objRS.getInt(1);
            }

            if(_Result == 1)
            {
                return true;
            }
        }
        catch(Exception ex){

            System.out.println(ex.getMessage());
            return false;
        }       

        return false;
    }
    /**
     * 根据用户名验证用户信息是否存在
     * @param name
     * @return
     */
    public boolean CheckUserName(String name){
        try{
            String _Sql = "select Count(*) from tb_UserLogin where userName ='" + name + "' ";

            ResultSet objRS = GetData(_Sql);
            int _Result = 0;
            while(objRS.next()){
                _Result = objRS.getInt(1);
            }

            if(_Result == 1)
            {
                return true;
            }
        }
        catch(Exception ex){

            System.out.println(ex.getMessage());
            return false;
        }       

        return false;
    }


    //获取全局用户信息(根据userName查询用户)
    public UserBean GetUser(String userName) {
        UserBean objBean = new UserBean();

        String _Sql = "select loginID, userName from tb_UserLogin where userName = '" + userName + "' ";
        ResultSet objRS = GetData(_Sql);
        try {
            while(objRS.next()){
                objBean.setLoginID(objRS.getString("loginID"));
                objBean.setUserName(objRS.getString("userName"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage().toString());
        }

        return objBean;


    }
    /**
     * 注册
     * @param user 增加信息
     * @return 1 成功 0失败
     */
    public int AddUserLogin(UserBean user){
        try{

            DB objDB = new DB();
            String _ID = objDB.GetServilID("tb_UserLogin", "loginID");
            String _Sql = "insert into tb_UserLogin(loginID, userName, userPwd) values('" + _ID + "', '"+user.getUserName()+"', '"+user.getUserPwd()+"')";
            int result = objDB.Add(_Sql);
            if(result==1)
            {

                System.out.println("数据增加成功，编号是：" + _ID);
                return 1;

            }
            else{
                return 0;
            }

        }
        catch(Exception e){

            System.out.println(e.getMessage());
        }
        return 0;       
    }

}