java 封装jdbc preparedstatement 绑定不定参数

发布网友发布时间：2022-04-30 22:47

共2个回答

懂视网时间：2022-05-01 03:08

myjdbc; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.sound.midi.MetaEventListener; import com.mysql.jdbc.Connection; import com.mysql.jdbc.Field; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.ResultSetMetaData; import domain.UserInfo; public class JdbcUtil { //表示数据库的用户名 private final String USERNAME = "root"; //表示数据库的密码 private final String PASSWORD = "hang796413"; //数据库的驱动信息 private final String DRIVER = "com.mysql.jdbc.Driver"; //访问数据库的地址 private final String URL = "jdbc:mysql://localhost:3306/gustomer"; //定义数据库的链接 private Connection connection; //定义sqk语句的执行对象 private java.sql.PreparedStatement pstmt; //定义查询返回的结果集合 private ResultSet resultSet; public JdbcUtil(){ try { Class.forName(DRIVER); System.out.println("注册驱动成功"); connection = (Connection) DriverManager.getConnection(URL,USERNAME,PASSWORD); System.out.println("链接成功"); } catch (Exception e) { } } /*完成对数据库的表的增删改的操作 * * @param sql * @param params * @retuen * @throws SQLException * */ public boolean upderbypaerdstaemnet(String sql, List<Object> params)throws SQLException{ boolean flag = false; int result = -1;//表示当用户执行增删改所影响数据库的行数 pstmt = connection.prepareStatement(sql); int index = 1; if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size();i++){ pstmt.setObject(index++, params.get(i)); } } System.out.println(pstmt.toString()); result = pstmt.executeUpdate(); System.out.println(result); flag = result > 0 ? true : false; return flag; } /*查询返回单条记录 * */ public Map<String,Object> findSimpleResult(String sql, List<Object> params)throws SQLException{ Map<String,Object> map = new HashMap<String,Object>(); //组合SQL语句 pstmt = connection.prepareStatement(sql); int index = 1; if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size();i++){ pstmt.setObject(index++, params.get(i)); } } System.out.println(pstmt.toString()); resultSet = pstmt.executeQuery();//返回查询结果 //获取列的信息 -> metaData ResultSetMetaData metaData = (ResultSetMetaData) resultSet.getMetaData(); int col_len = metaData.getColumnCount();//获得列的名称 System.out.println(col_len); //如果还有其他列 while(resultSet.next()){ for(int i = 0; i<col_len;i++){ //记录名字和值 System.out.println("11112333332233332"); //i+1!!!!!!!! String cols_name = metaData.getColumnName(i+1); System.out.println(cols_name); Object cols_value =resultSet.getObject(cols_name); if( cols_value == null){ cols_value = " "; } map.put(cols_name, cols_value); } } return map; } /* * 查询返回多行记录 * */ public List<Map<String,Object>> findMoreResult(String sql, List<Object> params)throws SQLException{ List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); pstmt = connection.prepareStatement(sql); int index = 1; if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size();i++){ pstmt.setObject(index++, params.get(i)); } } System.out.println(pstmt.toString()); resultSet = pstmt.executeQuery();//返回查询结果 //获取列的信息 -> metaData ResultSetMetaData metaData = (ResultSetMetaData) resultSet.getMetaData(); int col_len = metaData.getColumnCount();//获得列的名称 while(resultSet.next()){ //******多了这个地方****/ Map<String,Object> map = new HashMap<String,Object>(); for(int i = 0; i<col_len;i++){ //记录名字和值 String cols_name = metaData.getColumnName(i+1); Object cols_value =resultSet.getObject(cols_name); if( cols_value == null){ cols_value = " "; } map.put(cols_name, cols_value); } list.add(map); } return list; } //jdbc的封装可以使用反射机制来封装 public <T> T findSimpleRefResult(String sql ,List<Object>params, Class<T> cls)throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException { T resultObject = null; int index = 1; pstmt = connection.prepareStatement(sql); if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size();i++){ pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData =(ResultSetMetaData) resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while(resultSet.next()){ //通过反射机制创建实例 resultObject = cls.newInstance(); //初始化 for(int i = 0; i<cols_len;i++){ //记录名字和值 String cols_name = metaData.getColumnName(i+1); Object cols_value =resultSet.getObject(cols_name); if( cols_value == null){ cols_value = " "; } //获取javabean（UserInfo）对应列的属性 java.lang.reflect.Field field = cls.getDeclaredField(cols_name); //打开javabea的访问私有权限 field.setAccessible(true); //相对应的javabean进行赋值 field.set(resultObject,cols_value); } } return resultObject; } /* * 通过反射机制访问数据库 * */ public <T> List <T> finMoreRefResult(String sql ,List<Object>params, Class<T> cls)throws SQLException, IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException, InstantiationException{ List <T> list = new ArrayList<T>(); int index = 1; pstmt = connection.prepareStatement(sql); if(params != null && !params.isEmpty()){ for(int i = 0; i<params.size();i++){ pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData =(ResultSetMetaData) resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while(resultSet.next()){ //通过反射机制创建实例 T resultObject = cls.newInstance(); //初始化 for(int i = 0; i<cols_len;i++){ //记录名字和值 String cols_name = metaData.getColumnName(i+1); Object cols_value =resultSet.getObject(cols_name); if( cols_value == null){ cols_value = " "; } //获取javabean（UserInfo）对应列的属性 java.lang.reflect.Field field = cls.getDeclaredField(cols_name); //打开javabea的访问私有权限 field.setAccessible(true); //相对应的javabean进行赋值 field.set(resultObject,cols_value); } list.add(resultObject); } return list; } public void releaseConn(){ if(resultSet != null){ try { resultSet.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(pstmt != null){ try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(connection != null){ try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public static void main(String[] args){ JdbcUtil jdbcUtil = new JdbcUtil(); // String sql = "insert into userinfo(username,pswd) values(?,?)"; // List<Object> params = new ArrayList<Object>(); // params.add("rose"); // params.add("123"); // try { // boolean flag = jdbcUtil.upderbypaerdstaemnet(sql, params); // System.out.println(flag); // } catch (Exception e) { // } String sql ="select * from userinfo "; //查询单个 //List<Object>params = new ArrayList<Object>(); //params.add(1);//id为1的记录 try { List<UserInfo> list= jdbcUtil.finMoreRefResult(sql,null,UserInfo.class); System.out.println(list); } catch (Exception e) { }finally { jdbcUtil.releaseConn(); } } }

package domain;

import java.io.Serializable;

public class UserInfo implements Serializable {

 /**
 * 
 */
 private static final long serialVersionUID = 1L;
 private int id;
 private String username;
 private String pswd;
 
 public int getId() {
 return id;
 }

 public void setId(int id) {
 this.id = id;
 }

 

 @Override
 public String toString() {
 return "UserInfo [id=" + id + ", username=" + username + ", pswd=" + pswd + "]";
 }

 public String getUsername() {
 return username;
 }

 public void setUsername(String username) {
 this.username = username;
 }

 public String getPswd() {
 return pswd;
 }

 public void setPswd(String pswd) {
 this.pswd = pswd;
 }

 public UserInfo(){
 
 }
}

Mysql,JDBC封装

标签：

热心网友时间：2022-05-01 00:16

//数据库类
public class DB {
private Connection con;

private PreparedStatement pstm;

private String user = "root";

private String password = "xxx";

private String className = "org.gjt.mm.mysql.Driver";

private String url = "jdbc:mysql://localhost:3306/db_dzsw";

public DB()
{
try
{
Class.forName(className);
} catch (ClassNotFoundException e)
{
System.out.println("forname error");
e.printStackTrace();
}
}

public Connection getCon()
{
try
{
con = DriverManager.getConnection(url, user, password);
} catch (SQLException e)
{
System.out.println("connection");
con = null;
e.printStackTrace();
}
return con;
}

public void doPstm(String sql, Object[] params)
{
if (sql != null && !sql.equals(""))
{
if (params == null)
params = new Object[0];

getCon();
if (con != null)
{
try
{
System.out.println(sql);
pstm = con.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
for (int i = 0; i < params.length; i++)
{
pstm.setObject(i + 1, params[i]);
}
pstm.execute();
} catch (SQLException e)
{
System.out.println("doPstm()");
e.printStackTrace();
}
}
}
}

public ResultSet getRs() throws SQLException
{
return pstm.getResultSet();
}

public int getCount() throws SQLException
{
return pstm.getUpdateCount();
}

public void closed()
{
try
{
if (pstm != null)
pstm.close();
} catch (SQLException e)
{

e.printStackTrace();
}
try
{
if (con != null)
{
con.close();
}
} catch (SQLException e)
{

e.printStackTrace();
}
}

}
//调用
String sql="select * from k_student where id=?";
Object params[]={id};
DB db=new DB();
db.doPstm(sql, params);
ResultSet rs=db.getRs();
while(rs.next()){
//do something;
}
rs.close();
db.closed();