Java 中jdbc(java数据库连接)是一种用于执行SQL语句的java api,可以为多种关系数据库提供统一访问,这里以SQL SERVER数据库为例子,写一个连接数据库实现增删改的工具类。
开始需要建立数据库连接,声明四个字段username(用户名),password(密码),url(数据库地址),driver(驱动),如:
private static String username = "sa";
private static String password = "123";
private static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=EmperorSystem";
private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static Connection getConnection() {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
测试是否连接成功,可利用查询来测试:
public static void main(String[] args) throws SQLException {
String sql = "select * from SYS_MaterType";
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("Name"));
}
}
输出列名Name数据结果:
表示数据库连接成功。
每次用完Connection,PreparedStatement,ResultSet都需要释放资源,可封装一个方法来释放:
public static void close(Connection con, PreparedStatement ps, ResultSet rs) {
try {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
然后可以利用jdbc来封装增删查改的方法来减少相对应的代码量:
增删改:
private static Connection con;
private static PreparedStatement ps;
private static ResultSet rs;
public static boolean InUpDe(String sql, Object[] obj) {
boolean msg = false;
try {
con = Dbutil.getConnection();
con.setAutoCommit(false);
ps = con.prepareStatement(sql);
if (obj != null && obj.length > 0) {
for (int i = 0; i < obj.length; i++) {
ps.setObject(i + 1, obj[i]);
}
}
int res = ps.executeUpdate();
if (res > 0) {
msg = true;
con.commit();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
con.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} finally {
try {
con.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Dbutil.close(con, ps, rs);
}
return msg;
}
其中,Object[] obj是用来装需要的参数,先把事务自动提交关掉setAutoCommit(false),然后用循环把obj利用setObjec()方法代进sql语句里面,然后判断语句是否执行成功,成功就提交事务con.commit(),失败就回滚事务con.rollback()。