Java 连接 SqlServer工具类

1.下载 server2008R2驱动jar包

下载jar包

 http://www.microsoft.com/zh-cn/download/confirmation.aspx?id=21599

2.代码实现

 1）实体：

package entity;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable{
    private int id;
    private String username;
    private String password;
    private Date createtime;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    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 Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public User(int id, String username, String password, Date createtime) {
        super();
        this.id = id;
        this.username = username;
        this.password = password;
        this.createtime = createtime;
    }
    
    public User(String username, String password, Date createtime) {
        super();
        this.username = username;
        this.password = password;
        this.createtime = createtime;
    }
    public User() {
        super();
    
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password="
                + password + ", createtime=" + createtime + "]";
    }
    
}

2.dao

package dao;


import java.util.List;

import entity.User;

public interface UserDao {
    public void add(User u);
    
    public User getByUsername(String username);
    
    public void update(User u);
    
    public void delete(String username);
    
    public List<User> findAll();
}

3.ImplDao

package daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;



import java.util.List;

import jdbcutil.JdbcUtil;
import dao.UserDao;
import entity.User;



public class UserDaoImpl implements UserDao {

    @Override
    public void add(User u) {
        
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            con = JdbcUtil.getConnection();
            String sql = "insert into [user](username,password,createtime) values(?,?,?)";
            stmt= con.prepareStatement(sql);
            stmt.setString(1, "test");
            stmt.setString(2,"123456");
            stmt.setDate(3, new java.sql.Date(u.getCreatetime().getTime()));
            stmt.executeUpdate();
            
        } catch (Exception e) {
            
            e.printStackTrace();
        }finally
        {
            JdbcUtil.close(rs, stmt, con);
        }

    }

    @Override
    public User getByUsername(String username) {
        
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            con = JdbcUtil.getConnection();
            String sql = "select * from [user] where username=?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, username);
            rs = stmt.executeQuery();        
            while(rs.next())
            {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String password = rs.getString("password");
                Date time = rs.getDate("createtime");
                
                User u = new User(id,username,password,time);
                
                return u;
                
            }
        } catch (Exception e) {
            
            e.printStackTrace();
        }
        finally
        {
            
            JdbcUtil.close(rs, stmt, con);            
        }
        return null;
    }

    @Override
    public void update(User u) {
        
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            con = JdbcUtil.getConnection();
            
            String sql = "update [user] set password=? where username=?";
            
            stmt = con.prepareStatement(sql);
            
            stmt.setString(1, u.getPassword());
            
            stmt.setString(2, u.getUsername());
            
            stmt.executeUpdate();
            
        } catch (Exception e) {
            
            e.printStackTrace();
        }
        finally
        {
            
            JdbcUtil.close(rs, stmt, con);
        }
    }

    @Override
    public void delete(String username) {
        
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            con = JdbcUtil.getConnection();
            
            String sql = "delete from [user] where username=?";
            
            stmt = con.prepareStatement(sql);
            
            stmt.setString(1,username);
        
            stmt.executeUpdate();
            
        } catch (Exception e) {
            
            e.printStackTrace();
        }
        finally
        {
            
            JdbcUtil.close(rs, stmt, con);
        }
    }

    @Override
    public List<User> findAll() {
        List<User> list = new ArrayList<User>();
        
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            con = JdbcUtil.getConnection();
            String sql = "select * from [user]";
            
            stmt = con.prepareStatement(sql);
            
            rs = stmt.executeQuery();
            
            while(rs.next())
            {
                User u = new User(rs.getInt("id"), rs.getString("username"),rs.getString("password"),rs.getDate("createtime"));
                list.add(u);
                
            }
            return list;
            
        } catch (Exception e) {
            
            e.printStackTrace();
        }
        finally
        {
                JdbcUtil.close(rs, stmt, con);
            
        }
        
        return null;
    }
    


}

4.JDBC工具类

package jdbcutil;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;



public class JdbcUtil {
    
    private static String className;
    private static String url;
    private static String user;
    private static String password;
    
    static
    {
        
        try {
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("dbinfo.properties");
            
            Properties props = new Properties();
            props.load(in);
            
            className = props.getProperty("className");
            
            url = props.getProperty("url");
            
            user = props.getProperty("user");
            
            password = props.getProperty("password");
            
            //System.out.println(className);
            
            //System.out.println(url);
            
            //注册驱动
            Class.forName(className);
            
        } catch (Exception e) {
            
            e.printStackTrace();
        }
        
    }
    
    public static Connection getConnection() throws Exception
    {
        return DriverManager.getConnection(url, user, password);
        
    }
    
    public static void close(ResultSet rs, Statement stmt,Connection con)
    {
        
        if(rs!=null)
        {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
            rs= null;
        }
        
        if(stmt!=null)
        {
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
            stmt= null;
        }
        
        if(con!=null)
        {
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
            con= null;
        }
    }
}    

5.用于测试SQLServer驱动是否正确

package test;

import java.sql.Connection;
import java.sql.DriverManager;



public class JdbcTest {
    public static void test()
    {
        Connection conn = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            
            //注意url中没有microsoft才能使用，网上增加了microsoft的会出现No suitable driver found for//jdbc:microsoft:sqlserver:……的错误
            //若安装多个实例时，要确认相应的port及实例名
            String url="jdbc:sqlserver://localhost:1433;DatabaseName=JavaTestDB";
            String user="sa";
            String password="123456";
            conn= DriverManager.getConnection(url,user,password);
            
            System.out.println("链接成功");
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            if(conn!=null)
            {
                try {
                    conn.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                
                conn = null;
                
            }
            
        }
    
    }
}

备注： dbinfo.properties文件：

 

6.添加SQLServer jar包