java jdbc导出存储过程,Java JDBC连接数据库，反照创建实体类对象并赋值数据库行记录（支持存储过程）...

当前位置:我的异常网» 数据库 » Java JDBC连接数据库，反照创建实体类对象并赋值数

Java JDBC连接数据库，反照创建实体类对象并赋值数据库行记录(支持存储过程)

www.myexceptions.net  网友分享于：2014-05-29  浏览：2次

Java JDBC连接数据库，反射创建实体类对象并赋值数据库行记录(支持存储过程)

import java.lang.reflect.*;

import java.sql.*;

import java.util.*;

public class SqlHelper {

// SQL Server

/**

* JDBC驱动名称

*/

public static final String CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

/**

* 数据库连库字符串

*/

public static final String URL = "jdbc:sqlserver://192.168.1.254:1433;databaseName=BBSDB";

/**

* 用户名

*/

public static final String UID = "sa";

/**

* 密码

*/

public static final String PWD = "";

/**

* JDBC驱动类型

*/

public static Class CLS = null;

// Oracle

// public static final String CLASS_NAME =

// "oracle.jdbc.driver.OracleDriver";

// public static final String URL =

// "jdbc:oracle:thin:@localhost:1522:accp11g";

// public static final String UID = "system";

// public static final String PWD = "manager";

/**

* 获取数据库连接对象

*

* @return

* @throws ClassNotFoundException

* @throws SQLException

*/

public static Connection getConnection() throws ClassNotFoundException,

SQLException {

if (CLS == null) {

CLS = Class.forName(CLASS_NAME);

}

return DriverManager.getConnection(URL, UID, PWD);

}

/**

* 执行SQL语句不返回查询的操作，返回受影响的行数

*

* @param sql

*            SQL语句

* @return 受影响的行数

* @throws ClassNotFoundException

* @throws SQLException

*/

public static int executeNonQuery(String sql) {

int result = -1;

Connection con = null;

PreparedStatement ps = null;

try {

con = getConnection();

ps = con.prepareStatement(sql);

result = ps.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, ps, null);

}

return result;

}

/**

* 执行Insert语句，返回Insert成功之后标识列的值

*

* @param sql

* @return

* @throws ClassNotFoundException

* @throws SQLException

*/

public static int executeIdentity(String sql) {

int identity = -1;

Connection con = null;

Statement ps = null;

ResultSet rs = null;

try {

con = getConnection();

ps = con.createStatement();

ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

rs = ps.getGeneratedKeys();

if (rs.next()) {

// identity = rs.getInt("GENERATED_KEYS");

identity = rs.getInt(1);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, ps, null);

}

return identity;

}

/**

* 执行不返回结果集的存储过程

*

* @param sql

*            存储过程名称

* @param params

*            存储过程参数

* @throws ClassNotFoundException

* @throws SQLException

*/

public static void executeNonQuery(String sql, SqlParameter... params) {

Connection con = null;

CallableStatement cs = null;

try {

con = getConnection();

cs = con.prepareCall(sql);

setSqlParameter(cs, params);

cs.executeUpdate();

getSqlParameter(cs, params);

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, cs, null);

}

}

/**

* 执行返回聚合函数的操作

*

* @param sql

*            含有聚合函数的SQL语句

* @return 聚合函数的执行结果

* @throws SQLException

* @throws ClassNotFoundException

*/

public static int executeScalar(String sql) {

int result = -1;

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

con = getConnection();

ps = con.prepareStatement(sql);

rs = ps.executeQuery();

if (rs.next()) {

result = rs.getInt(1);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, ps, rs);

}

return result;

}

/**

* 执行返回泛型集合的SQL语句

*

* @param cls

*            泛型类型

* @param sql

*            查询SQL语句

* @return 泛型集合

* @throws ClassNotFoundException

* @throws SQLException

* @throws InstantiationException

* @throws IllegalAccessException

*/

public static List executeList(Class cls, String sql) {

List list = new ArrayList();

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

con = getConnection();

ps = con.prepareStatement(sql);

rs = ps.executeQuery();

while (rs.next()) {

T obj = executeResultSet(cls, rs);

list.add(obj);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, ps, rs);

}

return list;

}

/**

* 执行返回泛型集合的存储过程

*

* @param cls

*            泛型类型

* @param sql

*            存储过程名称

* @param params

*            存储过程参数

* @return 泛型集合

* @throws ClassNotFoundException

* @throws SQLException

* @throws InstantiationException

* @throws IllegalAccessException

*/

public static List executeList(Class cls, String sql,

SqlParameter... params) {

List list = new ArrayList();

Connection con = null;

CallableStatement cs = null;

ResultSet rs = null;

try {

con = getConnection();

cs = con.prepareCall(sql);

setSqlParameter(cs, params);

rs = cs.executeQuery();

while (rs.next()) {

T obj = executeResultSet(cls, rs);

list.add(obj);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, cs, rs);

}

return list;

}

/**

* 执行返回泛型类型对象的SQL语句

*

* @param cls

*            泛型类型

* @param sql

*            SQL语句

* @return 泛型类型对象

* @throws SQLException

* @throws ClassNotFoundException

* @throws InstantiationException

* @throws IllegalAccessException

*/

public static T executeEntity(Class cls, String sql) {

T obj = null;

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

con = getConnection();

ps = con.prepareStatement(sql);

rs = ps.executeQuery();

while (rs.next()) {

obj = executeResultSet(cls, rs);

break;

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, ps, rs);

}

return obj;

}

/**

* 执行返回泛型类型对象的存储过程

*

* @param cls

*            泛型类型

* @param sql

*            SQL语句

* @param params

*            存储过程参数

* @return 泛型类型对象

* @throws SQLException

* @throws ClassNotFoundException

* @throws InstantiationException

* @throws IllegalAccessException

*/

public static T executeEntity(Class cls, String sql,

SqlParameter... params) {

T obj = null;

Connection con = null;

CallableStatement cs = null;

ResultSet rs = null;

try {

con = getConnection();

cs = con.prepareCall(sql);

setSqlParameter(cs, params);

rs = cs.executeQuery();

while (rs.next()) {

obj = executeResultSet(cls, rs);

break;

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close(con, cs, rs);

}

return obj;

}

/**

* 将一条记录转成一个对象

*

* @param cls

*            泛型类型

* @param rs

*            ResultSet对象

* @return 泛型类型对象

* @throws InstantiationException

* @throws IllegalAccessException

* @throws SQLException

*/

private static T executeResultSet(Class cls, ResultSet rs)

throws InstantiationException, IllegalAccessException, SQLException {

T obj = cls.newInstance();

ResultSetMetaData rsm = rs.getMetaData();

int columnCount = rsm.getColumnCount();

// Field[] fields = cls.getFields();

Field[] fields = cls.getDeclaredFields();

for (int i = 0; i < fields.length; i++) {

Field field = fields[i];

String fieldName = field.getName();

for (int j = 1; j <= columnCount; j++) {

String columnName = rsm.getColumnName(j);

if (fieldName.equalsIgnoreCase(columnName)) {

Object value = rs.getObject(j);

field.setAccessible(true);

field.set(obj, value);

break;

}

}

}

return obj;

}

/**

* 设置存储过程参数名称，参数值，参数方向

*

* @param cs

* @param params

* @throws SQLException

*/

private static void setSqlParameter(CallableStatement cs,

SqlParameter... params) throws SQLException {

if (params != null) {

for (SqlParameter param : params) {

if (param.OutPut) {

String paramName = param.Name;

if (paramName == null || paramName.equals("")) {

cs.registerOutParameter(1, param.Type);// 设置返回类型参数

} else {

cs.registerOutParameter(paramName, param.Type);// 设置输出类型参数

}

} else {

cs.setObject(param.Name, param.Value);// 设置输入类型参数

}

}

}

}

/**

* 得到存储过程参数执行结果

*

* @param cs

* @param params

* @throws SQLException

*/

private static void getSqlParameter(CallableStatement cs,

SqlParameter... params) throws SQLException {

for (SqlParameter param : params) {

if (param.OutPut) {

String paramName = param.Name;

if (paramName == null || paramName.equals("")) {

param.Value = cs.getObject(1);// 返回类型参数值

} else {

param.Value = cs.getObject(paramName);// 输出类型参数值

}

}

}

}

/**

* 关闭JDBC对象，释放资源。

*

* @param con

*            连接对象

* @param ps

*            命令对象

* @param rs

*            结果集对象

* @throws SQLException

*/

private static void close(Connection con, Statement ps, ResultSet rs) {

try {

rs.close();

if (rs != null) {

rs = null;

}

if (ps != null) {

ps.close();

ps = null;

}

if (con != null) {

con.close();

con = null;

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

类SqlParameter

/**

* 存储过程参数类型

* @author Administrator

*

*/

public class SqlParameter {

/**

* 参数名称

*/

public String Name;

/**

* 参数值

*/

public Object Value;

/**

* true表示参数为输出类型

*/

public boolean OutPut;

/**

* 参数类型

*/

public int Type;

/**

* 输入类型参数的构造函数

* @param name 存储过程 输入类型 参数名称

* @param value 存储过程 输入类型 参数值

*/

public SqlParameter(String name,Object value){

this.Name = name;

this.Value= value;

}

/**

* 输出类型参数的构造函数

* @param type 存储过程 输出类型 参数类型

* @param name 存储过程 输出类型 参数名称

*/

public SqlParameter(int type,String name){

this.Name = name;

this.OutPut = true;

this.Type = type;

}

/**

* 返回类型参数的构造函数

* @param type 存储过程 返回类型

*/

public SqlParameter(int type){

this.Name = "";

this.OutPut = true;

this.Type = type;

}

}

类Program

import java.util.List;

public class Program {

public static void main(String[] args) {

// TODO Auto-generated method stub

String sql = "INSERT INTO [dbo].[UserInfo] ([UserName] ,[LoginName] ,[LoginPwd] ,[UserSex] ,[Birthday]) VALUES ('%s','%s','%s','%s','%s')";

sql = String.format(sql, "wyp1","wyp1","wyp1","1","1981-04-21");

int identyValue = SqlHelper.executeIdentity(sql);

System.out.println(String.format("Identity Value:%d",identyValue));

//        List list = SqlHelper.executeList(UserInfo.class, "select * from UserInfo");

//        for (UserInfo userInfo : list) {

//            System.out.println(String.format(

//                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",

//                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));

//        }

//        SqlParameter param = new SqlParameter("sortField", "[UserInfoId] DESC");

//        List list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectAll(?)}",param);

//        for (UserInfo userInfo : list) {

//            System.out.println(String.format(

//                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",

//                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));

//        }

//        SqlParameter paramSortField = new SqlParameter("sortField", "[UserInfoId] DESC");

//        SqlParameter paramPageSize = new SqlParameter("pageSize", 10);

//        SqlParameter paramPageIndex = new SqlParameter("pageIndex", 1);

//        SqlParameter paramWhere = new SqlParameter("where", "1=1");

//        List list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectByPagerParams(?,?,?,?)}",paramSortField,paramPageSize,paramPageIndex,paramWhere);

//        for (UserInfo userInfo : list) {

//            System.out.println(String.format(

//                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",

//                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));

//        }

//        SqlParameter paramWhere = new SqlParameter("where", "1=1");

//        SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount");

//        SqlHelper.executeNonQuery("{call dbo.UserInfoCountByWhere(?,?)}", paramWhere,paramRecordCount);

//        if(paramRecordCount.Value instanceof Integer){

//            Integer recordCount = (Integer)paramRecordCount.Value;

//            System.out.println(String.format("RecordCount:%d",recordCount));

//        }

//        SqlParameter paramWhere = new SqlParameter("where", "1=1");

//        SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount");

//        SqlParameter paramReturnValue = new SqlParameter(java.sql.Types.INTEGER);

//        SqlHelper.executeNonQuery("{? = call dbo.UserInfoCountByWhere(?,?)}", paramReturnValue,paramWhere,paramRecordCount);

//        if(paramRecordCount.Value instanceof Integer){

//            Integer recordCount = (Integer)paramRecordCount.Value;

//            System.out.println(String.format("RecordCount:%d",recordCount));

//        }

//        if(paramReturnValue.Value instanceof Integer){

//            Integer returnValue = (Integer)paramReturnValue.Value;

//            System.out.println(String.format("ReturnValue:%d",returnValue));

//        }

}

}

文章评论