JDBC连接SQLServer数据库(简易代码)
时间:2022-03-13 22:57
1 package pmsdao; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 9 public class BaseDAO { 10 // 驱动类全名(包名.类名) 11 private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; 12 // 连接的URL 13 private static final String URL = "jdbc:sqlserver://localhost\\sqlexpress:1433;DatabaseName=terminalEquipment"; 14 // 登录SQLserver用户名和密码 15 private static final String USERNAME = "sa"; 16 private static final String PWD = "ghp418512"; 17 18 // 数据库对象声明 19 private static PreparedStatement pst = null; 20 private static ResultSet rs = null; 21 private static Connection con = null; 22 23 /** 24 * 加载驱动 25 */ 26 static { 27 try { 28 Class.forName(DRIVER); 29 } catch (ClassNotFoundException e) { 30 // TODO Auto-generated catch block 31 e.printStackTrace(); 32 } 33 } 34 35 /** 36 * 建立连接 37 */ 38 public static Connection getCon() { 39 try { 40 con = DriverManager.getConnection(URL, USERNAME, PWD); 41 return con; 42 } catch (SQLException e) { 43 // TODO Auto-generated catch block 44 e.printStackTrace(); 45 } 46 return null; 47 } 48 49 /** 50 * 执行查询 51 * 52 * @param sql 53 * 执行的参数化SQL语句 54 * @param params 55 * object数组,封装所有SQL语句参数 56 * @return ResultSet 返回执行后的结果集 57 */ 58 public static ResultSet execQuery(String sql, Object[] params) { 59 try { 60 getCon(); 61 pst = con.prepareStatement(sql); 62 63 setPrepareStatementParams(params); 64 rs = pst.executeQuery(); 65 } catch (SQLException e) { 66 // TODO Auto-generated catch block 67 e.printStackTrace(); 68 } 69 return rs; 70 71 } 72 73 /** 74 * 执行增删改SQL操作方法 75 * 76 * @param sql 77 * 执行的参数化SQL语句 78 * @param params 79 * object数组,封装所有SQL语句参数 80 * @return 受影响的行数,-1表示出现异常 81 */ 82 public int execUpdate(String sql, Object[] params) { 83 84 getCon(); 85 try { 86 pst = con.prepareStatement(sql); 87 88 setPrepareStatementParams(params); 89 90 int affectRows = pst.executeUpdate(); 91 return affectRows; 92 } catch (SQLException e) { 93 // TODO Auto-generated catch block 94 e.printStackTrace(); 95 } finally { 96 free(rs, pst, con); 97 } 98 return -1; 99 100 } 101 102 /** 103 * 为PrepareStatement设置参数 104 * 105 * @param params 106 * 参数数组 107 * @throws SQLException 108 */ 109 private static void setPrepareStatementParams(Object[] params) 110 throws SQLException { 111 if (params != null) { 112 for (int i = 0; i < params.length; i++) { 113 pst.setObject(i + 1, params[i]); 114 } 115 } 116 } 117 118 /** 119 * 关闭Connection,PrepareStatement,Result 120 * 121 * @param rs 122 * @param pst 123 * @param con 124 */ 125 public static void free(ResultSet rs, PreparedStatement pst, Connection con) { 126 try { 127 if (rs != null) { 128 rs.close(); 129 } 130 if (pst != null) { 131 pst.close(); 132 } 133 if (con != null) { 134 con.close(); 135 } 136 } catch (SQLException e) { 137 e.printStackTrace(); 138 } 139 140 } 141 }