SQL server使用

DBCon.java

package dao;

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

/**
 *
 * @author Administrator
 */
public class DBCon {
    public static  Connection  getConnection(){
         Connection  dbConnection = null;
         try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            dbConnection = DriverManager.getConnection(
                     "jdbc:sqlserver://localhost:1433;databaseName=firstdb",
                     "sa", "123");
             System.out.println("链接成功"); 
         }catch(Exception e){
            
             e.printStackTrace();
         }
         return dbConnection;
    }

    public static void closeConnection(Connection  dbConnection){
        try{
            if(dbConnection != null && !dbConnection.isClosed()){
                dbConnection.close();
                System.out.println("关闭成功"); 
            }
           
        }catch(SQLException sqlEx){
           
            sqlEx.printStackTrace();
        }
    }

    public static void closeStatement(PreparedStatement pStatement){
        try{
            if(pStatement != null){
                pStatement.close();
                pStatement = null;
            }
        }catch(SQLException sqlEx){
            sqlEx.printStackTrace();
        }
    }

    public static void closeResultSet(ResultSet  res){
        try{
            if(res != null){
                res.close();
                res = null;
            }
        }catch(SQLException sqlEx){
            sqlEx.printStackTrace();
        }
    }


    
    
    public static void main(String[] args)    
    {  
       
        System.out.println("数据库连接中……");   
        Connection  dbConnection = getConnection();  
        closeConnection(dbConnection);
                }  
}

UserDAO.java

package dao;

import static dao.DBCon.getConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import test.User;

/**
 *
 * @author ASUS
 */
public class UserDAO {
    
    
    public static int insertOne(User em)
    {
      int result = 0;
      Connection con = null;
      PreparedStatement pst = null;
        try{
             con = getConnection();
             String strSql = " insert into dbo.Tab1(username,password) values(?,?)";
             pst = con.prepareStatement(strSql);
                pst.setString(1, em.getUsername());
                pst.setString(2, em.getPassword());
             result = pst.executeUpdate();
        }catch(SQLException e){
          e.printStackTrace();
          }finally{
            DBCon.closeStatement(pst);
            DBCon.closeConnection(con);   
            } 
        return result;
    }
    
    public static int deleteOne(String username)
    {
       int result = 0;
      Connection con = null;
        PreparedStatement pst = null;
        try{
             con = getConnection();
             String strSql = " delete from dbo.Tab1 where username=?";
             pst = con.prepareStatement(strSql);
             
             pst.setString(1, username);
            
             result = pst.executeUpdate();
        }catch(SQLException e){
          e.printStackTrace();
        }finally{
            DBCon.closeStatement(pst);
            DBCon.closeConnection(con);
            
        } 
        return result;
    }
     
    public static int updataOne(User em)
    {
        int result = 0;
        Connection con = null;
        PreparedStatement pst = null;
        try{
             con = getConnection();
             String strSql = " update dbo.Tab1 set password=? where username=?";
            pst = con.prepareStatement(strSql);
              pst.setString(1, em.getPassword());
              pst.setString(2, em.getUsername());
            result = pst.executeUpdate();
        }catch(SQLException e){
          e.printStackTrace();
        }finally{
            DBCon.closeStatement(pst);
            DBCon.closeConnection(con);
        }
      return result;
    }
      
      public static User queryOne(String us){
        User em =null;
        Connection con =null;
        PreparedStatement pst = null;
        ResultSet rst = null;
        try{
            con =DBCon.getConnection();
            String sql = "select * from dbo.Tab1 where username=?";
            pst = con.prepareStatement(sql);
                pst.setString(1, us);
            rst = pst.executeQuery();
            if(rst.next()){
               em = new User();
               em.setUsername(rst.getString(1));
               em.setPassword(rst.getString(2));
            }
        }catch(SQLException e){
          e.printStackTrace();
          }
        finally{
          DBCon.closeResultSet(rst);
          DBCon.closeStatement(pst);
          DBCon.closeConnection(con);
          return em;
        }   
      }
       
    
     
    public static ArrayList queryMore(String us)
       {
         ArrayList<User> empList = new ArrayList<User>();
        Connection con =null;
        PreparedStatement pst = null;
        ResultSet rst = null;
        try{
            con =DBCon.getConnection();
            String sql = "select * from dbo.Tab1 where username=?";
            pst = con.prepareStatement(sql);
            pst.setString(1, us);
            rst = pst.executeQuery();
            while(rst.next()){
              User em = new User();
               em.setUsername(rst.getString(1));
               em.setPassword(rst.getString(2));
               empList.add(em);
            }
        }catch(SQLException e){
          e.printStackTrace();
          }
        finally{
          DBCon.closeResultSet(rst);
          DBCon.closeStatement(pst);
          DBCon.closeConnection(con);
          return empList;
          }   
        }
}

User.java

public class User {
    private String username;
    private String password;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public User() {
    }
    
}

Test.java

package test;

import dao.UserDAO;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author ASUS
 */
public class Test {
   
    public static void main(String[] args)    
    {  
       System.out.println("数据库操作进行中……");
//        UserDAO.deleteOne("龙傲天");
        
//        User em =  new User("gqp","csb"); 
//        UserDAO.insertOne(em);
//        UserDAO.updataOne(em);

        String us ="gqp";
//        User em = UserDAO.queryOne(us);
//        System.out.println(em.getUsername()+" "+em.getPassword());
        
       
        ArrayList list = new ArrayList();
        list = UserDAO.queryMore(us);
        for (Iterator it = list.iterator(); it.hasNext();) {
            User tmp = (User) it.next();
            System.out.println(tmp.getUsername()+" "+tmp.getPassword());   
        }
        System.out.println("数据库操作完成 啦啦啦");
                }  
    
}