实体类信息:
package com.gupaoedu.vip.spring.transaction.entity;
import lombok.Data;
import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="t_member")
@Data
public class Member implements Serializable {
@Id private Long id;
private String name;
private String addr;
private Integer age;
public Member(String name, String addr, Integer age) {
this.name = name;
this.addr = addr;
this.age = age;
}
public Member() {
}
@Override
public String toString() {
return "Member{" +
"id=" + id +
", name='" + name + '\'' +
", addr='" + addr + '\'' +
", age=" + age +
'}';
}
}
具体实现及测试类:
package com.gupaoedu.vip.spring.transaction.test;
import com.gupaoedu.vip.spring.transaction.entity.Member;
import javax.persistence.Table;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class JdbcTest {
public static void main(String[] args) {
/* // 纯粹的JDBC实现测试
String sql = "select * from t_member";
List<Member> list = selectBySql(sql);
for(Member member : list){
System.out.println(member);
}*/
// 通过反射实现ORM测试
Member condition = new Member();
condition.setName("tom");
condition.setAge(22);
List<?> result = select(condition);
for(Object member : result){
System.out.println(member);
}
}
public static List<?> select(Object condition) {
List<Object> result = new ArrayList<>();
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gp_spring_tx","root","123456");
String sql = createSql(condition);
pstm = con.prepareCall(sql);
rs = pstm.executeQuery();
int i=1;
while (rs.next()){
result.add(mapperRowToObject(rs, condition));
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
pstm.close();
con.close();
}catch (Exception e1){
e1.printStackTrace();
}
}
return result;
}
public static String createSql(Object condition) throws IllegalAccessException {
Class<?> entityClass = condition.getClass();
String table = entityClass.getAnnotation(Table.class).name();
StringBuffer sql = new StringBuffer();
sql.append("select * from ").append(table).append(" where 1 = 1 ");
Field[] fields = entityClass.getDeclaredFields();
for(Field field : fields){
field.setAccessible(true);
Object value = field.get(condition);
if(null != value){
if(String.class == field.getType()){
sql.append(" and "+field.getName()+" = '"+value+"'");
}else {
sql.append(" and "+field.getName()+" = "+value);
}
}
}
return sql.toString();
}
private static List<Member> selectBySql(String sql){
List<Member> result = new ArrayList<>();
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gp_spring_tx","root","123456");
pstm = con.prepareCall(sql);
rs = pstm.executeQuery();
int i=1;
while (rs.next()){
result.add(mapperRow(rs, i++));
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
pstm.close();
con.close();
}catch (Exception e1){
e1.printStackTrace();
}
}
return result;
}
private static Member mapperRow(ResultSet rs, int i) throws Exception{
Member instance = new Member();
instance.setId(rs.getLong("id"));
instance.setAddr(rs.getString("addr"));
instance.setName(rs.getString("name"));
instance.setAge(rs.getInt("age"));
return instance;
}
private static Object mapperRowToObject(ResultSet rs, Object condition) throws Exception{
Object instance = condition.getClass().newInstance();
Field[] fields = condition.getClass().getDeclaredFields();
for(Field field : fields){
field.setAccessible(true);
if(String.class == field.getType()){
field.set(instance, rs.getString(field.getName()));
}else if(Long.class == field.getType()){
field.set(instance, rs.getLong(field.getName()));
}else{
field.set(instance, rs.getInt(field.getName()));
}
}
return instance;
}
}