JDBC原生程序
项目结构：
Dao.java文件：
package com.sk.jdbc.dao;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.sk.jdbc.util.ConnectionUtil;
//Dao类中存放通用的数据访问方法
public class Dao<T> {
private Class<T> clazz;
@SuppressWarnings("unchecked")
public Dao() {
Type type = this.getClass().getGenericSuperclass(); // courseDao对象
if (type instanceof ParameterizedType) {
ParameterizedType parameterizedType = (ParameterizedType) type;
Type[] types = parameterizedType.getActualTypeArguments();
if (types != null && types.length > 0) {
if (types[0] instanceof Class) {
clazz = (Class<T>) types[0];
}
}
}
}
// 通用的查询方法----查单个记录
public T queryOne(String sql, Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
T entity = null;
try {
connection = ConnectionUtil.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
resultSet = ps.executeQuery();
if (resultSet != null) {
ResultSetMetaData rsmd = resultSet.getMetaData();
int colCounts = rsmd.getColumnCount();
if (resultSet.next() == true) { // 使内部游标推进到下一条记录
// *****获取查询集中有几列，各个列的名称是什么？----通过ResultSetMetaData实现
entity = clazz.newInstance();
for (int i = 0; i < colCounts; i++) {
String colName = rsmd.getColumnLabel(i + 1);
System.out.println(colName);
Object colValue = resultSet.getObject(i + 1); // //
// colValue="450101";
Field field = clazz.getDeclaredField(colName);
field.setAccessible(true);
if (colValue instanceof BigDecimal) {
double value = ((BigDecimal) colValue)
.doubleValue();
System.out.println(value);
field.set(entity, value);
} else {
field.set(entity, colValue);
}
field.setAccessible(false);
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ConnectionUtil.release(resultSet, ps, connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return entity;
}
// 通用的update()方法：
public void update(String sql, Object... args) throws Exception {
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = ConnectionUtil.getConnection();
preparedStatement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} finally {
try {
ConnectionUtil.release(null, preparedStatement, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 通用的查询方法----查多个记录
public List<T> queryList(String sql, Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
T entity = null;
List<T> list=null;
try {
connection = ConnectionUtil.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
resultSet = ps.executeQuery();
if (resultSet != null) {
ResultSetMetaData rsmd = resultSet.getMetaData(); //获取结果集元数据
int colCounts = rsmd.getColumnCount();
list=new ArrayList<>();
while(resultSet.next()) { // 使内部游标推进到下一条记录
// *****获取查询集中有几列，各个列的名称是什么？----通过ResultSetMetaData实现
entity = clazz.newInstance();
for (int i = 0; i < colCounts; i++) {
String colName = rsmd.getColumnLabel(i + 1);
Object colValue = resultSet.getObject(i + 1); // //
// colValue="450101";
Field field = clazz.getDeclaredField(colName);
field.setAccessible(true);
if (colValue instanceof BigDecimal) {
double value = ((BigDecimal) colValue)
.doubleValue();
System.out.println(value);
field.set(entity, value);
} else {
field.set(entity, colValue);
}
field.setAccessible(false);
}
list.add(entity);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ConnectionUtil.release(resultSet, ps, connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
}
CourseDao.java
package com.sk.jdbc.dao;
import com.sk.jdbc.entity.Course;
public class CourseDao extends Dao<Course> {
public CourseDao() {
super();
}
}
StudentDao.java
package com.sk.jdbc.dao;
import com.sk.jdbc.entity.Student;
public class StudentDao extends Dao<Student> {
}
Course.java
package com.sk.jdbc.entity;
import java.sql.Date;
//ORM：
public class Course {
//java.sql.Timestamp
private String c_no;
private String c_name;
private String c_type;
private double c_credit;
private int c_time;
private Date c_begin;
public Course() {
// TODO Auto-generated constructor stub
}
public Course(String c_no, String c_name, String c_type, double c_credit,
int c_time) {
super();
this.c_no = c_no;
this.c_name = c_name;
this.c_type = c_type;
this.c_credit = c_credit;
this.c_time = c_time;
}
public String getC_no() {
return c_no;
}
public void setC_no(String c_no) {
this.c_no = c_no;
}
public String getC_name() {
return c_name;
}
public void setC_name(String c_name) {
this.c_name = c_name;
}
public String getC_type() {
return c_type;
}
public void setC_type(String c_type) {
this.c_type = c_type;
}
public double getC_credit() {
return c_credit;
}
public void setC_credit(double c_credit) {
this.c_credit = c_credit;
}
public Integer getC_time() {
return c_time;
}
public void setC_time(Integer c_time) {
this.c_time = c_time;
}
@Override
public String toString() {
return "Course [c_no=" + c_no + ", c_name=" + c_name + ", c_type="
+ c_type + ", c_credit=" + c_credit + ", c_time=" + c_time
+ ", c_begin=" + c_begin
+"]";
}
public Date getC_begin() {
return c_begin;
}
public void setC_begin(Date c_begin) {
this.c_begin = c_begin;
}
}
Student.java
package com.sk.jdbc.entity;
import java.util.Date;
import java.util.Arrays;
//orm:
/**
*
* @author lal
* 2020-9-5 下午2:05:34
* com.sk.jdbc.entity
* jdbc_3
* Student.java
* TODO
*/
/*s_id int
s_no char
s_name varchar
s_gender char
s_birth datetime
s_profession varchar
s_class varchar
s_phone char
s_addr varchar
s_photo blob*/
public class Student {
private int s_id;
private String s_no;
private String s_name;
private String s_gender;
private Date s_birth;
private String s_profession;
private String s_class;
private String s_phone;
private String s_addr;
private byte[]s_photo;
public Student() {
// TODO Auto-generated constructor stub
}
public Student(int s_id, String s_no, String s_name, String s_gender,
Date s_birth, String s_profession, String s_class, String s_phone,
String s_addr, byte[] s_photo) {
super();
this.s_id = s_id;
this.s_no = s_no;
this.s_name = s_name;
this.s_gender = s_gender;
this.s_birth = s_birth;
this.s_profession = s_profession;
this.s_class = s_class;
this.s_phone = s_phone;
this.s_addr = s_addr;
this.s_photo = s_photo;
}
public int getS_id() {
return s_id;
}
public void setS_id(int s_id) {
this.s_id = s_id;
}
public String getS_no() {
return s_no;
}
public void setS_no(String s_no) {
this.s_no = s_no;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public String getS_gender() {
return s_gender;
}
public void setS_gender(String s_gender) {
this.s_gender = s_gender;
}
public Date getS_birth() {
return s_birth;
}
public void setS_birth(Date s_birth) {
this.s_birth = s_birth;
}
public String getS_profession() {
return s_profession;
}
public void setS_profession(String s_profession) {
this.s_profession = s_profession;
}
public String getS_class() {
return s_class;
}
public void setS_class(String s_class) {
this.s_class = s_class;
}
public String getS_phone() {
return s_phone;
}
public void setS_phone(String s_phone) {
this.s_phone = s_phone;
}
public String getS_addr() {
return s_addr;
}
public void setS_addr(String s_addr) {
this.s_addr = s_addr;
}
public byte[] getS_photo() {
return s_photo;
}
public void setS_photo(byte[] s_photo) {
this.s_photo = s_photo;
}
@Override
public String toString() {
return "Student [s_id=" + s_id + ", s_no=" + s_no + ", s_name="
+ s_name + ", s_gender=" + s_gender + ", s_birth=" + s_birth
+ ", s_profession=" + s_profession + ", s_class=" + s_class
+ ", s_phone=" + s_phone + ", s_addr=" + s_addr + ", s_photo="
+ Arrays.toString(s_photo) + "]";
}
}
ConnectionUtile.java
package com.sk.jdbc.util;
import java.io.BufferedInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ConnectionUtil {
public static Connection getConnection() throws Exception {
BufferedInputStream bis = new BufferedInputStream(ConnectionUtil.class
.getClassLoader().getResourceAsStream("conn.properties"));
Connection conn = null;
Properties properties = new Properties();
properties.load(bis);
String driverClassName = properties.getProperty("driverclass");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Class.forName(driverClassName); // 加载数据库驱动类(数据库厂商)，同时实现了注册
conn = DriverManager.getConnection(url, username, password);// 获取连接
return conn;
}
public static void release(ResultSet resultSet, Statement statement,
Connection conn) throws SQLException {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
}
}
conn.properties
driverclass=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/stuinfodb?characterEncoding=utf8 username=root password=root
QueryTest.java
package com.sk.jdbc.test;
import org.junit.Test;
import com.sk.jdbc.dao.CourseDao;
import com.sk.jdbc.dao.StudentDao;
import com.sk.jdbc.entity.Course;
import com.sk.jdbc.entity.Student;
public class QueryTest {
@Test
public void testCourseDaoQuery() {
CourseDao courseDao = new CourseDao();
String sql1 = "SELECT c_no,c_name,c_type,c_credit,c_time,c_begin FROM t_course WHERE c_no=?"; //
Course course1 = courseDao.queryOne(sql1, "450101");
System.out.println(course1);
}
@Test
public void testStudentDaoQuery() {
String sql = "select s_id,s_no,s_name,s_birth,s_gender,s_class,s_phone from t_student where s_id=?";
StudentDao studentDao = new StudentDao();
Student student = studentDao.queryOne(sql, 1);
System.out.println(student);
}
}
sql.sql
select * from t_course where c_no='450101'; SELECT c_no,c_name,c_time FROM t_course WHERE c_time=(select max(c_time) from t_course); select s_id,s_no,s_name,s_birth,s_gender,s_class,s_phone from t_student where s_id=1;
本文来自博客园，作者：极地阳光-ing，转载请注明原文链接：https://www.cnblogs.com/Polar-sunshine/p/13690395.html
