JDBC连接MySQL和SQL Server的工具类及SQL Server连接报错的解决办法

简单的JDBC连接，没有用c3p0之类的连接方式，所有连接信息也直接放在了工具类里，有需要的自己去改

JDBCUtils1.java

package mysqlutils;  
  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
import com.mysql.jdbc.Connection;  
import com.mysql.jdbc.PreparedStatement;  
  
public class JDBCUtils1 {  
  
    private static final String DBDRIVER = "com.mysql.jdbc.Driver";// 驱动类类名  
  
    private static final String DBNAME = "school";// 数据库名  
  
    private static final String DBURL = "jdbc:mysql://localhost:3306/" + DBNAME;// 连接URL  
  
    private static final String DBUSER = "root";// 数据库用户名  
  
    private static final String DBPASSWORD = "tjw19951105";// 数据库密码  
  
    private static Connection conn = null;  
  
    private static PreparedStatement ps = null;  
  
    private static ResultSet rs = null;  
  
    /* 
     * 获取数据库连接 
     */  
    public static Connection getConnection() {  
  
        try {  
  
            Class.forName(DBDRIVER);// 注册驱动  
  
            conn = (Connection) DriverManager.getConnection(DBURL, DBUSER,  
                    DBPASSWORD);// 获得连接对象  
            System.out.println("成功加载MYSQL驱动程序");  
        } catch (ClassNotFoundException e) {// 捕获驱动类无法找到异常  
  
            System.out.println("找不到MYSQL驱动程序");  
            System.out.println(e.toString());  
            e.printStackTrace();  
  
        } catch (SQLException e) {// 捕获SQL异常  
  
            e.printStackTrace();  
        }  
  
        return conn;  
  
    }  
  
    public static ResultSet select(String sql) throws Exception {  
  
        try {  
  
            conn = getConnection();  
  
            ps = (PreparedStatement) conn.prepareStatement(sql);  
  
            rs = ps.executeQuery(sql);  
  
            return rs;  
  
        } catch (SQLException sqle) {  
  
            throw new SQLException("select data Exception: "  
                    + sqle.getMessage());  
  
        } catch (Exception e) {  
  
            throw new Exception("System error: " + e.getMessage());  
  
        }  
  
    }  
  
    /* 
     * 增删改均调用这个方法 
     */  
    public static void updata(String sql) throws Exception {  
  
        try {  
  
            conn = getConnection();  
  
            ps = (PreparedStatement) conn.prepareStatement(sql);  
  
            ps.executeUpdate();  
  
        } catch (SQLException sqle) {  
  
            throw new SQLException("insert data Exception: "  
                    + sqle.getMessage());  
  
        } finally {  
  
            try {  
  
                if (ps != null) {  
  
                    ps.close();  
  
                }  
  
            } catch (Exception e) {  
  
                throw new Exception("ps close exception: " + e.getMessage());  
  
            }  
  
            try {  
  
                if (conn != null) {  
  
                    conn.close();  
  
                }  
  
            } catch (Exception e) {  
  
                throw new Exception("conn close exception: " + e.getMessage());  
  
            }  
  
        }  
  
    }  
}  

JDBCUtils2.java

package sqlserverutils;  
  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
public class JDBCUtils2 {  
  
    private static final String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";// 驱动类类名  
  
    private static final String DBNAME = "school";// 数据库名  
//  jdbc:sqlserver://localhost:1433;DatabaseName=school","sa","tjw19951105"  
    private static final String DBURL = "jdbc:sqlserver://localhost:1433;DatabaseName="+DBNAME;// 连接URL  
  
    private static final String DBUSER = "sa";// 数据库用户名  
  
    private static final String DBPASSWORD = "tjw19951105";// 数据库密码  
  
    private static Connection conn = null;  
  
    private static PreparedStatement ps = null;  
  
    private static ResultSet rs = null;  
  
    /* 
     * 获取数据库连接 
     */  
    public static Connection getConnection() {  
  
        try {  
  
            Class.forName(DBDRIVER);// 注册驱动  
  
            conn = DriverManager.getConnection(DBURL,DBUSER,  
                    DBPASSWORD);// 获得连接对象  
            System.out.println("成功加载SQL Server驱动程序");  
        } catch (ClassNotFoundException e) {// 捕获驱动类无法找到异常  
  
            System.out.println("找不到SQL Server驱动程序");  
            System.out.println(e.toString());  
            e.printStackTrace();  
  
        } catch (SQLException e) {// 捕获SQL异常  
  
            e.printStackTrace();  
        }  
  
        return conn;  
  
    }  
  
    public static ResultSet select(String sql) throws Exception {  
  
        try {  
  
            conn = getConnection();  
  
            ps = (PreparedStatement) conn.prepareStatement(sql);  
  
            rs = ps.executeQuery();  
  
            return rs;  
  
        } catch (SQLException sqle) {  
  
            throw new SQLException("select data Exception: "  
                    + sqle.getMessage());  
  
        } catch (Exception e) {  
  
            throw new Exception("System error: " + e.getMessage());  
  
        }  
  
    }  
  
    /* 
     * 增删改均调用这个方法 
     */  
    public static void updata(String sql) throws Exception {  
  
        try {  
  
            conn = getConnection();  
  
            ps = (PreparedStatement) conn.prepareStatement(sql);  
  
            ps.executeUpdate();  
  
        } catch (SQLException sqle) {  
  
            throw new SQLException("insert data Exception: "  
                    + sqle.getMessage());  
  
        } finally {  
  
            try {  
  
                if (ps != null) {  
  
                    ps.close();  
  
                }  
  
            } catch (Exception e) {  
  
                throw new Exception("ps close exception: " + e.getMessage());  
  
            }  
  
            try {  
  
                if (conn != null) {  
  
                    conn.close();  
  
                }  
  
            } catch (Exception e) {  
  
                throw new Exception("conn close exception: " + e.getMessage());  
  
            }  
  
        }  
  
    }  
}  

Student.java

package domain;  
  
public class Student {  
    int sno;  
    String sname;  
    int sage;  
    String ssex;  
  
    public Student(int sno, String sname, int sage, String ssex) {  
        super();  
        this.sno = sno;  
        this.sname = sname;  
        this.sage = sage;  
        this.ssex = ssex;  
    }  
    public int getSno() {  
        return sno;  
    }  
    public void setSno(int sno) {  
        this.sno = sno;  
    }  
    public String getSname() {  
        return sname;  
    }  
    public void setSname(String sname) {  
        this.sname = sname;  
    }  
    public int getSage() {  
        return sage;  
    }  
    public void setSage(int sage) {  
        this.sage = sage;  
    }  
    public String getSsex() {  
        return ssex;  
    }  
    public void setSsex(String ssex) {  
        this.ssex = ssex;  
    }  
    @Override  
    public String toString() {  
        return "Student [sno=" + sno + ", sname=" + sname + ", sage=" + sage  
                + ", ssex=" + ssex + "]";  
    }  
  
}  

Test.java

package MainClass;  
  
import java.sql.ResultSet;  
import java.util.ArrayList;  
import java.util.List;  
  
import domain.Student;  
import mysqlutils.JDBCUtils1;  
  
/** 
 * 1.插入删除修改写法其实都是一样的，唯一的区别在于SQL语句不同，直接替换相应的SQL语句就行了，下边我分开写是为了看的更清楚 
 * 2.用之前只需要去JDBCUtils1中修改DBNAME、DBPASSWORD就好，如果不是root用户则顺带改了DBUSER 
 * 3.查询的调用方法:ResultSet rs = JDBCUtils1.select(sql); 
 * 4.增删改查调用的方法:JDBCUtils1.updata(sql); 
 * 5.注意把我libs包下的jar包导入并添加到path中 
 * 6.如果需要使用通配符，参考课本339页Example11_5.java,这里JDBCUtils1.getConnection()获取连接以后，自己往下写几行代码就出来了 
 *  有问题就try——catch捕获，这个不好封装 
 * @author Lenovo_PC 
 * 
 */  
public class Test {  
  
    public static void main(String[] args) {  
        // TODO Auto-generated method stub  
//      selectData();//查询数据  
  
//      insertData();//插入数据  
  
//      deleteData();//删除数据  
  
//      updateDate();//修改数据  
    }  
  
    /** 
     * 修改数据 
     */  
    private static void updateDate() {  
        System.out.println("修改MySQL数据库数据");  
        String sql = "update student set sname = 'amy' where sname = 'tom'";  
        try {  
            JDBCUtils1.updata(sql);  
            System.out.println("修改成功！");  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * 删除数据 
     */  
    private static void deleteData() {  
        System.out.println("删除MySQL数据库数据");  
        String sql = "delete from student where sname = 'tom'";  
        try {  
            JDBCUtils1.updata(sql);  
            System.out.println("删除成功！");  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * 插入数据 
     */  
    private static void insertData() {  
        System.out.println("插入MySQL数据库数据");  
        //这里注意Sno，最好别重复，不过没设置主键，重复也没啥关系  
        String sql = "insert into student (sno, sname, sage, ssex) values (4, 'tom', 18, '女')";  
        try {  
            JDBCUtils1.updata(sql);  
            System.out.println("插入成功！");  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * 查询数据 
     */  
    private static void selectData() {  
        System.out.println("查询MySQL数据库数据");  
        String sql = "select * from student";  
        List<Student> list = new ArrayList<Student>();  
        try {  
            ResultSet rs = JDBCUtils1.select(sql);  
            while (rs.next()) {  
                list.add(new Student(rs.getInt("sno"), rs.getString("sname"), rs.getInt("sage"), rs.getString("ssex")));  
            }  
            for (int i = 0; i < list.size(); i++) {  
                System.out.println(list.get(i));  
            }  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
}  

Test2.java

package MainClass;  
  
import java.sql.ResultSet;  
import java.util.ArrayList;  
import java.util.List;  
  
import mysqlutils.JDBCUtils1;  
import sqlserverutils.JDBCUtils2;  
import domain.Student;  
  
/** 
 * 测试SQL Server的类，直接复制的Test 
 * 就是把JDBCUtils1改为JDBCUtils2 
 * @author Lenovo_PC 
 * 
 */  
public class Test2 {  
  
    public static void main(String[] args) {  
        // TODO Auto-generated method stub  
//      selectData();//查询数据  
  
//      insertData();//插入数据  
  
//      deleteData();//删除数据  
  
//      updateDate();//修改数据  
    }  
  
    /** 
     * 修改数据 
     */  
    private static void updateDate() {  
        System.out.println("修改SQL Server数据库数据");  
        String sql = "update student set sname = 'amy' where sname = 'tom'";  
        try {  
            JDBCUtils2.updata(sql);  
            System.out.println("修改成功！");  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * 删除数据 
     */  
    private static void deleteData() {  
        System.out.println("删除SQL Server数据库数据");  
        String sql = "delete from student where sname = 'tom'";  
        try {  
            JDBCUtils2.updata(sql);  
            System.out.println("删除成功！");  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * 插入数据 
     */  
    private static void insertData() {  
        System.out.println("插入SQL Server数据库数据");  
        //这里注意Sno，最好别重复，不过没设置主键，重复也没啥关系  
        String sql = "insert into student (sno, sname, sage, ssex) values (4, 'tom', 18, '女')";  
        try {  
            JDBCUtils2.updata(sql);  
            System.out.println("插入成功！");  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
    /** 
     * 查询数据 
     */  
    private static void selectData() {  
        System.out.println("查询SQL Server数据库数据");  
        String sql = "select * from student";  
        List<Student> list = new ArrayList<Student>();  
        try {  
            ResultSet rs = JDBCUtils2.select(sql);  
            while (rs.next()) {  
                list.add(new Student(rs.getInt("sno"), rs.getString("sname"), rs.getInt("sage"), rs.getString("ssex")));  
            }  
            for (int i = 0; i < list.size(); i++) {  
                System.out.println(list.get(i));  
            }  
        } catch (Exception e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
    }  
  
}  

注意!!

SQL Server数据库连接的时候可能会报错，报错情况如下：

文字描述:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: Connection refused: connect. Please verify the connection properties and check that a SQL Server instance is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.

解决的办法:TCP/IP协议给禁止了。点击“SQL Server Configuration Manager” -> “SQL Server XXX Network Configuration -> Protocols for MSSQL SERVER -> TCP/IP，如果是被禁止了，就启动一下