如何使用JDBC实现基础数据插入操作？

本文共计707个文字，预计阅读时间需要3分钟。

javapackage org.tlgg.utils;import java.lang.reflect.*;import java.sql.*;import java.util.ArrayList;import java.util.List;import org.tlgg.model.Roles;import org.tlgg.model.Users;

public class DBTools { // 创建一个静态全局变量 private static Connection connection;

// 初始化数据库连接 public static void initConnection(String url, String user, String password) { try { connection=DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); } }

// 获取所有用户 public static List getAllUsers() { List users=new ArrayList(); try { Statement stmt=connection.createStatement(); ResultSet rs=stmt.executeQuery(SELECT * FROM users); while (rs.next()) { Users user=new Users(); user.setId(rs.getInt(id)); user.setUsername(rs.getString(username)); user.setPassword(rs.getString(password)); user.setRole(new Roles(rs.getInt(role_id))); users.add(user); } rs.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } return users; }}

package org.tlgg.utils; import java.lang.reflect.*; import java.sql.*; import java.util.ArrayList; import java.util.List; import org.tlgg.model.Roles; import org.tlgg.model.Users; public class DBTools { //创建一个静态全局变量conn private static Connection conn; //放在静态初始化块中，每次一加载类就把驱动加载到内存中 static{ try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //新建一个连接返回 private static Connection getConnection(){ try { conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","123456"); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static int insertObject(T type){ int i=0; List columns = new ArrayList (); //通过反射获取类，然后获取该类的所有属性，保存到columns中 Class clazz = type.getClass(); for(Field field : clazz.getDeclaredFields()){ //当属性的类型为字符串或者整型的时候，添加到columns中 if((field.getType().equals(String.class))||(field.getType().equals(int.class))){ columns.add(field.getName()); } } //最后一位个属性role为引用类型，需要移除掉 //columns.remove(columns.size()-1); String sql = createInsertSql(clazz, columns); System.out.println(sql); try { PreparedStatement preparedStatement = getConnection().prepareStatement(sql); int index = 1; for(String param:columns){ //getDeclaredMethods()和getMethods()的区别是：前者只会返回当前类中声明的方法，不会返回继承与其他类的方法 for(Method method :clazz.getDeclaredMethods()){ if(method.getName().equalsIgnoreCase("get"+param)){ //给sql语句中后面的？设值为type对象调用当前method的返回值 preparedStatement.setObject(index, method.invoke(type, null)); index++; } } } i = preparedStatement.executeUpdate(); } catch (SQLException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) { e.printStackTrace(); } return i; } /** * 通过泛型T的包装类和String对象的参赛集合 生成插入的字符串返回 * @param Class c,List columns * @return String 插入的sql语句 * */ private static String createInsertSql(Class c,List columns){ StringBuffer buffer = new StringBuffer(); buffer.append("insert into "); buffer.append(c.getSimpleName()); buffer.append("("); //通过增强for循环取出columns中的要插入参数的列名 for(String param : columns){ buffer.append(param); buffer.append(","); } //最后一个列名不需要,要) buffer.delete(buffer.length()-1, buffer.length()); buffer.append(") "); //目前语句为：insert into users (id,name,password,roleid) buffer.append("values("); for(String param : columns){ buffer.append("?"); buffer.append(","); } buffer.delete(buffer.length()-1, buffer.length()); buffer.append(")"); //目前语句为：insert into users (id,name,password,roleid) valuse(?,?,?,?) return buffer.toString(); } public static void main(String[] args){ List columns = new ArrayList(); Users user = new Users(12307,"友善哥","123",2,null); System.out.println(insertObject(user)); //System.out.println(String.class); //System.out.println(int.class); /*Class clazz = Users.class; for(Field field : clazz.getDeclaredFields()){ columns.add(field.getName()); System.out.println(field.getName()); } columns.remove(columns.size()-1); for (Object object : columns) { System.out.println(object); } System.out.println(createInsertSql(clazz, columns)); //System.out.println(); for(Method method :clazz.getDeclaredMethods()){ System.out.println(method.getName()); }*/ } }