验证中...
开源中国 2018 年度最后一场技术盛会邀你来约~错过就要等明年啦!点此立即预约
语言: Java
分类: 编程语言基础
最后更新于 2018-08-15 21:05
片段 1 片段 2 片段 3
SqlHelpr.java
原始数据 复制代码
package utils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
public class SqlHelpr {
private static Properties properties=new Properties();
public static final String DRIVER="driver";
public static final String URL="url";
public static final String USER="user";
public static final String PASSWORD="password";
static ThreadLocal<Connection> local=new ThreadLocal<Connection>();
/**
* 获取驱动
* @param
*/
static{
try {
properties.load(SqlHelpr.class.getClassLoader().getResourceAsStream("jdbc.properties"));
} catch (IOException e) {
e.printStackTrace();
}
}
static{
try {
Class.forName(properties.getProperty(DRIVER));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
*链接数据库
*/
public static Connection openConnection(){
Connection conn=local.get();
try {
if(conn==null||conn.isClosed()){
conn=DriverManager.getConnection(properties.getProperty(URL),properties.getProperty(USER),properties.getProperty(PASSWORD));
local.set(conn);
}
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
*查询
*/
public static ResultSet select(String sql,Object ... parmes){
ResultSet rs=null;
PreparedStatement pst=null;
try {
Connection conn=SqlHelpr.openConnection();
pst=conn.prepareStatement(sql);
if(parmes!=null){
for(int i=0;i<parmes.length;i++){
pst.setObject(i+1,parmes[i]);
}
}
rs=pst.executeQuery();
} catch (SQLException e) {
throw new RuntimeException("查询失败",e);
}
return rs;
}
/**
*查询2
* @param <T>
*/
public static <T> List<T> select2(String sql,RowHandlerMapper<T> rowhandlermapper,Object ... parmes){
ResultSet rs=null;
PreparedStatement pst=null;
List<T> rows=null;
try {
Connection conn=SqlHelpr.openConnection();
pst=conn.prepareStatement(sql);
if(parmes!=null){
for(int i=0;i<parmes.length;i++){
pst.setObject(i+1,parmes[i]);
}
}
rs=pst.executeQuery();
rows=rowhandlermapper.maping(rs);
} catch (SQLException e) {
throw new RuntimeException("查询失败",e);
} finally{
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return rows;
}
/**
*查询3
* @param <T>
*/
public static List<HashMap<String,Object>> select3(String sql,Object ... parmes){
ResultSet rs=null;
PreparedStatement pst=null;
List<HashMap<String,Object>> rows=new ArrayList<HashMap<String,Object>>();
try {
Connection conn=SqlHelpr.openConnection();
pst=conn.prepareStatement(sql);
if(parmes!=null){
for(int i=0;i<parmes.length;i++){
pst.setObject(i+1,parmes[i]);
}
}
rs=pst.executeQuery();
ResultSetMetaData res=rs.getMetaData();
int l=res.getColumnCount();
while(rs.next()){
HashMap<String,Object> maps=new HashMap<String,Object>();
for(int i=0;i<l;i++){
String label=res.getColumnLabel(i+1);
maps.put(label,rs.getObject(label));
}
rows.add(maps);
}
} catch (SQLException e) {
throw new RuntimeException("查询失败",e);
} finally{
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return rows;
}
/**
*修改
*/
public static int update(String sql,Object ... parmes){
int row=0;
PreparedStatement pst=null;
Connection conn=SqlHelpr.openConnection();
try {
pst=conn.prepareStatement(sql);
if(parmes.length!=0){
for(int i=0;i<parmes.length;i++){
pst.setObject(i+1,parmes[i]);
}
}
row=pst.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("修改失败",e);
} finally{
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return row;
}
/**
* 关闭
*/
public static void closeConnection(){
Connection conn=local.get();
if(conn!=null){
try {
conn.close();
conn=null;
local.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static interface RowHandlerMapper<T>{
public List<T> maping(ResultSet rs);
}
}
Test.java
原始数据 复制代码
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import utils.SqlHelpr;
import utils.SqlHelpr.RowHandlerMapper;
public class Test {
public static void main(String[] args) {
String sql="select * from user";
//查询2
// try{
// List<User> users=SqlHelpr.select2(sql,new RowHandlerMapper<User>(){
// @Override
// public List<User> maping(ResultSet rs) {
// List<User> users=new ArrayList<User>();
// if(rs!=null){
// try {
// while(rs.next()){
// User u=new User();
// u.setId(rs.getString("id"));
// u.setName(rs.getString("name"));
// users.add(u);
// }
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
// return users;
// }});
// for(User u:users){
// System.out.println(u.getName());
// }
// }catch(Exception e){
// e.printStackTrace();
// System.out.println("执行失败");
// }
//查询3
try{
List<HashMap<String,Object>> maps=SqlHelpr.select3(sql);
for(HashMap<String,Object> map:maps){
System.out.println(map.get("name"));
}
}catch(Exception e){
e.printStackTrace();
System.out.println("执行失败");
}
}
}
class User{
String id;
String name;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
jdbc.properties

评论列表( 2 )

1859385_donghuirong
李昱 2018-08-15 21:05

是啊,受益匪浅

很久. 2018-06-12 21:34

受益匪浅啊。。

你可以在登录后,发表评论

搜索帮助

12_float_left_people 12_float_left_close