import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class sqlhelp {
private static String dirverName = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/cardmanager";
private static String user = "root";
private static String password = "root";
private static Connection connection = null;
private Connection getConnection() {
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
// 编写静态代码块(比构造函数加载更快),用来加载驱动类
static {
try {
Class.forName(dirverName);
// 创建连接对象
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 执行sql(增删改)返回受影响的行数
public int executesql(String sql) {
Connection conn = null;
Statement sta = null;
int result = 0;
try {
conn = getConnection();
sta = conn.createStatement();
result=sta.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
close(null, sta, conn);
}
return result;
}
public List<CardInfo>getQuery(String sql){
Connection conn=null;
Statement sta=null;
ResultSet res=null;
List<CardInfo> cilist=new ArrayList<CardInfo>();
try {
conn=getConnection();
sta=conn.createStatement();
res=sta.executeQuery(sql);
while (res.next()) {
CardInfo cd=new CardInfo();
cd.setCardId(res.getInt(1));
cd.setCardType(res.getInt(2));
cd.setCardBalance(res.getInt(3));
cd.setIsSaled(res.getInt(4));
cilist.add(cd);
}
} catch (Exception e) {
// TODO: handle exception
}
finally{
close(res, sta, conn);
}
return cilist;
}
public void close(ResultSet res, Statement sta, Connection conn) {
if (res != null) {
try {
res.close();
} catch (Exception e) {
e.printStackTrace();
}
if (sta != null) {
try {
sta.close();
} catch (Exception e) {
e.printStackTrace();
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
}