数据库工具类封装

package com.nan.util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class SqlHelper {
static final String driver = "com.mysql.jdbc.Driver";
static final String url = "jdbc:mysql://localhost:3306/grocery";
static final String username = "root";
static final String password = "lisha226";
static {
// 加载驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
static Connection ct = null;
static PreparedStatement ps = null;
static ResultSet rs = null;


// 定义查询的方法
public static List querys(String sql, String[] promaters) {
List li = new ArrayList();

try {
// 创建连接
ct = DriverManager.getConnection(url, username, password);
ps = ct.prepareStatement(sql);
if (promaters != null) {
for (int i = 0; i < promaters.length; i++) {

ps.setString(i + 1, promaters[i]);



}
}
System.out.println(ps.toString());
rs = ps.executeQuery();
int rowNumber = rs.getMetaData().getColumnCount();// 返回表中的列数


while (rs.next()) {
Object[] obj = new Object[rowNumber];


for (int i = 0; i < obj.length; i++) {
obj[i] = rs.getObject(i + 1);
}

li.add(obj);
}


return li;
/*
* while(rs.next()){ Users u1=new Users(); int id= rs.getInt("id");
* String username=rs.getString("userName"); String
* email=rs.getString("email"); int grade=rs.getInt("grade"); String
* password=rs.getString("password"); u1.setId(id);
* u1.setUserName(username); u1.setEmail(email); u1.setGrade(grade);
* u1.setPassword(password); li.add(u1); }
*/
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, ps, ct);
}
return null;
}


// 定义关闭流的方法
public static void close(ResultSet rs, PreparedStatement ps, Connection ct) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (ct != null) {
try {
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


}


// 定义删除外键的方法
public static void dropKey(String sql){
try {
ct = DriverManager.getConnection(url, username, password);
ps = ct.prepareStatement(sql);
ps.execute(sql);
} catch (Exception e) {
e.printStackTrace();
}finally{
close(rs, ps, ct);
}
}


// 定义查询之外的语句
public static void annother(String sql[], Object[][] parameter) {
// 获取连接
try {
ct = DriverManager.getConnection(url, username, password);
// 用户可能创建多个sql语句
ct.setAutoCommit(false);// 将自动提交设置为手动提交模式
for (int i = 0; i < sql.length; i++) {
if (parameter[i] != null) {
ps = ct.prepareStatement(sql[i]);
for (int j = 0; j < parameter[i].length; j++) {
ps.setObject(j + 1, parameter[i][j]);
}
}
System.out.println(ps);
ps.executeUpdate();


}
ct.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
close(rs, ps, ct);
}


}


}