JDBC(JAVA DATABASE CONNECTITVITY)
JDBC
mysql是数据库，navicat是数据库的一个客户端，前提打开数据库的服务，从客户端给数据库发送sql命令，执行命令，返回执行之后的结果，使用完之后要关闭数据库的连接。
连接步骤：
1、准备需要获得连接的数据：（可以放在文件里面存）
用户名username、密码password、url连接地址：
（jdbc:mysql://localhost:3306/数据库名称）、驱动(com.mysql.jdbc.Driver)
2、下载jar包，ad为图书馆，获得mysql连接对象--->java.sql.Connection
2.1、注册驱动：
将MySQL的驱动对象注册到jvm里面、jvm加载驱动class文件
驱动管理器注册驱动：
try{
Calss.forName(dirver);
}catch(ClassNotFoundException e){
}
3、通过驱动器管理器获得连接对象：
Connection connection=DriverManager.getConnection(url,username,password);
因为再JDBC里面META-INF自动加载了驱动，但是在web里面不可以省略，通过idea集成数据库得添加驱动，手动点击MySQL驱动，选择驱动jar包，连接之前需要测试连接，测试显示成功应用， 就可以在idea的***@localhost写sql语句了。也可以利用代码来连接数据库。
1、将sql语句发送到mysql服务中，将sql语句存储到PreparedStatement对象中
PreparedStatement statement = connection.prepareStatement(sql);
2、执行：
statement.executeUpdate()；//增删改
ResultSet resultSet = statement.executeQuery();//查
但是他们都继承了AutoCloseable,因此都需要释放资源。
占位符： ？
select * from db_department where dep_id=?
//有了占位符之后还需要赋值，通过statement.set**(index,值);索引从一开始
在java中小数都是近似值，因此如果需要准确的小数，需要用bigDecimal来给 值。
date日期格式转换：
据目前已知，数据库的date和util的date是两个不同的类，当数据库里面的date是java.sql.Date,而业务层是java.util.Date的时候，需要进行格式转换：
sql.Date里面有一个方法：
new Date(Long date)；
可以看出这是专门进行格式转换的，我们可以通过util.Date下面的getTime将Date转换成long，然后在传入sql日期类的构造中，进行日期格式化
我们可以直接通过表来创建实体类：但是需要修改配置文件Generate POJOs.groovy里面的一些内容：
packageName = "com.sample;"
typeMapping = [
(~/(?i)int/) : "long",
(~/(?i)float|double|decimal|real/): "double",
(~/(?i)datetime|timestamp/) : "java.sql.Timestamp",
(~/(?i)date/) : "java.sql.Date",
(~/(?i)time/) : "java.sql.Time",
(~/(?i)/) : "String"
]
然后：右键某张表—–>scripts Extensions——>Generate POJOs.groovy---->选择路径，创建实体类，然后点开实体类，将需要的一些包导入即可。
代码如下：
1、从常量获取
public class Constants {
public static final String USER_NAME="root",PASSWORD="123456";
public static final String URL="jdbc:mysql://localhost:3306/study_db?useSSL=false";
//后面的？useSSL=false是为了解决警告：You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
public static final String DRIVER="com.mysql.jdbc.Driver";
}
//建立连接，一般放在工具类：
public class JDBCUtils {
/**
* 建立连接
* @return
*/
public static Connection getConnection() {
try {
Class.forName(Constants.DRIVER);
return DriverManager.getConnection(Constants.URL, Constants.USER_NAME, Constants.PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭资源
* @param connection
* @param statement
*/
public static void close(Connection connection, Statement statement) {
try {
if(connection!=null&&!connection.isClosed())
connection.close();
if(statement!=null&&!statement.isClosed())
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
close(connection,statement);
try {
if(resultSet!=null&&!resultSet.isClosed())
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 是否连接
* @param connection
* @return
*/
public static boolean isConnection(Connection connection){
try {
if(connection.isClosed())
return false;
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
}
2、从文件获取：存放路径module下面的resource目录，并且标记resource为根目录 ：通过：p.load(当前.class.getClassLoader().getResourceAsStream("xxx.yyy"));来读取数据,.getClassLoader()表示当前目录为根目录
##连接需要的数据
user=root
password=123456
url=jdbc:mysql://localhost:3306/study_db?useSSL=false
driver=com.mysql.jdbc.Driver
private static void demo2() {
Properties p=new Properties();
try {
p.load(JDBCDemo.class.getClassLoader().getResourceAsStream("jdbc.properties"));
Class.forName(p.getProperty("driver"));
Connection connection = DriverManager.getConnection(p.getProperty("url"), p.getProperty("user"), p.getProperty("password"));
System.out.println(!connection.isClosed());
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
但是上面的statement不是很好，因为我们不可能保证所有的条件都是确定的，而在使用statement的时候会有注入问题 ：
statement.executeUpadate(sql+"or 1=1")
此时换成PreparedStatement来进行参数化赋值
insert into db_department values(?,?)
//？表示参数，这里表示两个参数
statement.setInt(1,department.getDepId());
//第一个数字表示第几个参数，后面是值
statement.setString(2,department.getDepName());
将Java和数据库连接起来进行基础增删改查：
实体类：根据表的列来设置实体类的属性
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Employee {
private Integer emId;
private Integer emDepId;
private String emName;
private int emAge;
private String emGender;
private int emExp;
private Date emEmplyeeTime;
private Byte emStatus;
}
实现数据层：连接数据库，往数据库的表里面里面添加数据（只做与数据库相关的）
public class EmployDao {
/**
* add data into database
* @param employee
* @return
*/
public int add(Employee employee) {
int i = 0;
Connection connection = GetConnection.getConnections();
if (!GetConnection.isConnection(connection)) return -1;
String sql = "insert into db_employee values(null,?,?,?,?,?,?,?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, employee.getEmDepId());
statement.setString(2, employee.getEmName());
statement.setInt(3, employee.getEmAge());
statement.setString(4, employee.getEmGender());
statement.setInt(5, employee.getEmAge());
statement.setDate(6, new Date(employee.getEmEmplyeeTime().getTime()));
statement.setByte(7, employee.getEmStatus());
i = statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
GetConnection.close(connection, statement);
}
return i;
}
/**
* delete information according to em_id
* @param id
* @return
*/
public int delete(int id) {
Connection connection = GetConnection.getConnections();
if (!GetConnection.isConnection(connection)) return -1;
int i = 0;
PreparedStatement statement = null;
String sql = "delete from db_employee where em_id=?";
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
i = statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
GetConnection.close(connection, statement);
}
return i;
}
/**
* update employee'information by em_id
* @param employee
* @return
*/
public int update(Employee employee) {
Connection connection = GetConnection.getConnections();
if (!GetConnection.isConnection(connection)) return -1;
int i = 0;
String sql = "update db_employee set em_name=? where em_id=?";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setString(1, employee.getEmName());
statement.setInt(2, employee.getEmId());
i = statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
GetConnection.close(connection, statement);
}
return i;
}
/**
* get employee by em_id
* @param id
* @return
*/
public Employee getEmployee(int id) {
Connection connection = GetConnection.getConnections();
if (!GetConnection.isConnection(connection)) return null;
Employee employee = null;
String sql = "select * from db_employee where em_id=?";
PreparedStatement statement = null;
ResultSet resultSet=null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
resultSet= statement.executeQuery();
if (resultSet.next()) {
employee = new Employee(resultSet.getInt("em_id"),
resultSet.getInt("em_dep_id"), resultSet.getString("em_name"),
resultSet.getInt("em_age"), resultSet.getString("em_gender"),
resultSet.getInt("em_exp"), resultSet.getDate("em_emplyee_time"),
resultSet.getByte("em_status"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
GetConnection.close(connection,statement,resultSet);
}
return employee;
}
/**
* query information from database
* @return
*/
public List<Employee> selectAll() {
Connection connection = GetConnection.getConnections();
if (!GetConnection.isConnection(connection)) return null;
List<Employee> employees = new ArrayList<>(10);
PreparedStatement statement = null;
ResultSet resultSet = null;
String sql = "select * from db_employee order by em_id";
try {
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
employees.add(new Employee(resultSet.getInt("em_id"),
resultSet.getInt("em_dep_id"), resultSet.getString("em_name"),
resultSet.getInt("em_age"), resultSet.getString("em_gender"),
resultSet.getInt("em_exp"), resultSet.getDate("em_emplyee_time"),
resultSet.getByte("em_status")));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
GetConnection.close(connection, statement, resultSet);
}
return employees;
}
}
业务层：创建表格对应的实体类对象，并且进行赋值，再将对象传入实现数据层（所有与业务相关的操作）
public class EmployeeService {
private Scanner scanner = new Scanner(System.in);
private EmployDao dao = new EmployDao();
public void add() {
System.out.println("请输入新增的员工信息：员工编号、部门编号、姓名、年龄、性别、工龄、入职时间、在职状态【0、在职，1、离职】");
Employee employee = new Employee(scanner.nextInt(), scanner.nextInt(),
scanner.next(), scanner.nextInt(), scanner.next(), scanner.nextInt(),DateForm.strToDate(scanner.next()), scanner.nextByte());
//业务层实现外键约束
JDBCDao dao1 = new JDBCDao();
List<Department> select = dao1.select();
int index = -1, i = 0;
for (Department department : select) {
if (department.getDepId() == employee.getEmDepId()) index = i;
i++;
}
if (index == -1) {
System.out.println("该部门不存在！");
return;
}
int add = dao.add(employee);
if (add > 0) {
System.out.println("添加成功！");
} else System.out.println("添加失败！");
}
public void delete() {
System.out.println("请输入要删除的员工编号：");
int i = scanner.nextInt();
System.out.println("您想要删除的员工信息为：" + dao.getEmployee(i));
int delete = dao.delete(i);
if (delete > 0) {
System.out.println("删除成功！");
} else System.out.println("删除失败！");
}
public void update() {
System.out.println("请输入需要修改的员工编号：");
int i = scanner.nextInt();
Employee employee = dao.getEmployee(i);
System.out.println(employee);
System.out.println("请输入修改后的员工姓名：");
employee.setEmName(scanner.next());
dao.update(employee);
}
public void select() {
dao.selectAll().forEach(System.out::println);
}
}
放在单元测试包junit里面启动
@Test//爆红alt+enter导入包 这是一个单元测试包
public void demo3() {
DepaService.selectAll();
DepaService.add();
DepaService.delete();
DepaService.update();
}
由于上面增删改很多代码都是重复的，我们可以优化一下:
将增删改部分放入工具类：
public class GetConnection {
public static Connection getConnections() {
try {
Class.forName(Constants.DRIVER);
return DriverManager.getConnection(Constants.URL, Constants.USER_NAME, Constants.PWD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static boolean isConnection(Connection connection) {
try {
if (connection.isClosed()) return false;
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public static void close(Connection connection, Statement statement) {
try {
if (connection != null && !connection.isClosed())
connection.isClosed();
if (statement != null && !statement.isClosed())
statement.isClosed();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
close(connection, statement);
try {
if (resultSet != null && !resultSet.isClosed())
resultSet.isClosed();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int update(String sql,Object...args){
int i=0;
Connection connections = getConnections();
if(!isConnection(connections))return -1;
PreparedStatement statement=null;
try {
statement=connections.prepareStatement(sql);
if(args==null||args.length==0)return -1;
for (int i1 = 0; i1 < args.length; i1++) {
statement.setObject((i1+1),args[i]);
}
i=statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(connections,statement);
}
return i;
}
}
dao层（数据持久层）增删改部分就可以简化：
public int add(Employee employee) {
String sql = "insert into db_employee values(null,?,?,?,?,?,?,?)";
return GetConnection.update(sql,employee.getEmDepId(),
employee.getEmName(),employee.getEmAge(),employee.getEmGender(),
employee.getEmExp(),employee.getEmEmplyeeTime(),employee.getEmStatus());
}
/**
* delete information according to em_id
* @param id
* @return
*/
public int delete(int id) {
String sql = "delete from db_employee where em_id=?";
return GetConnection.update(sql,id);
}
/**
* update employee'information by em_id
* @param employee
* @return
*/
public int update(Employee employee) {
String sql = "update db_employee set em_name=? where em_id=?";
return GetConnection.update(sql,employee.getEmName(),employee.getEmId());
}
连接反射思想写查询语句，思路如下：
1、连接数据库、获取声明语句对象
2、获得每一行中每一列的元素，通过元素来获取对应的路径和列名，有了路径就可以得到属性的类型，有了列名就可以得到对应的set方法
3、传入类，通过类名来getMethod，我这里数据库里面的名字都是以下划线分开，而Java中属性都是以首字母大写来划分，但是java的set语句里面是set加上首字母大写的形式
4、自定义一个得到对应方法名的方法，然后通过方法注入值
5、装入到集合
1、先把前面连接写好：
public static <T> List<T> select(String sql, Class t) {
Connection connection = getConnections();
if (!isConnection(connection)) return null;
List<T> list = new ArrayList<>(10);
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
Object tObject = t.newInstance();
//得到每一行的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//得到表格的列数
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
//获得每列列名
String columnLabel = metaData.getColumnLabel((i + 1));
//获得每列类的存放路径
String columnClassName = metaData.getColumnClassName((i + 1));
//获得每列值
Object valueObject = resultSet.getObject(columnLabel);
//获取方法名
String methodName = getMethodName(columnLabel);
//获取方法的返回值类型
Class methodReturn = getClass(columnClassName);
//注入值
setValue(t, valueObject, tObject, methodName, methodReturn);
}
list.add((T) tObject);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
GetConnection.close(connection, statement, resultSet);
}
return list;
}
2、根据列名来获取方法名
/**
* 根据列名来获取方法名（列名有下划线，利用下划线分隔，然后首字母大写）
*
* @param label
* @return
*/
private static String getMethodName(String label) {
StringBuilder builder = new StringBuilder("set");
String[] split = label.split("_");
for (String s : split) {
builder.append(Character.toUpperCase(s.charAt(0)));
builder.append(s.substring(1));
}
return builder.toString();
}
3、根据类型存放路径得到类型
/**
* 根据类型存放路径得到类型
*
* @param classPath
* @return
*/
private static Class getClass(String classPath) {
if (classPath.equals("java.sql.Date")) {
classPath = "java.util.Date";
}
try {
return Class.forName(classPath);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
4、通过method对象往对象里面赋值
/**
* 注入值
*
* @param t
* @param valueObject
* @param tObejct
* @param methodName
* @param methodReturn
*/
private static void setValue(Class t, Object valueObject, Object tObejct, String methodName, Class methodReturn) {
try {
Method method = t.getMethod(methodName, methodReturn);
method.setAccessible(true);
method.invoke(tObejct, valueObject);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
当然有了上面的思想，其实也可以通过反射来获取表名，然后利用拼接完善sql语句。主要还是要注意属性名【xxYyy】和表的列名【xx_yy】得对应上。
上面属于自己敲的，也可以利用工具来直接查询【我这里用的是commons-dbutils-x.x.jar】x.x为版本号，因为有多种版本我就没写明确的版本。
/**
* 利用DBUtils工具jar包来进行增删改
*
* @param sql
* @param args
* @return
*/
public static int updateByJar(String sql, Object... args) {
int i = 0;
Connection connection = getConnections();
if (!isConnection(connection)) return -1;
try {
i = new QueryRunner().update(connection, sql, args);
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
/**
* 利用jar包来进行查询全部结果集
*
* @param sql
* @param t
* @param args
* @param <T>
* @return 结果集
*/
public static <T> List<T> queryReturnList(String sql, Class<T> t, Object... args) {
Connection connections = getConnections();
if (!isConnection(connections)) return null;
List<T> list = new ArrayList<>(10);
try {
list = new QueryRunner().query(sql, new BeanListHandler<>(t), args);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 根据条件查询单个对象
* @param sql
* @param t
* @param args
* @param <T>
* @return 对象
*/
public static <T> T queryReturnObject(String sql,Class<T> t,Object...args) {
Connection connections = getConnections();
if (!isConnection(connections)) return null;
T t1=null;
try {
t1 = new QueryRunner().query(connections, sql, new BeanHandler<>(t), args);
} catch (SQLException e) {
e.printStackTrace();
}
return t1;
}
public static void main(String[] args) {
String sql = "delete from db_employee where em_id=?";
updateByJar(sql, 23);
System.out.println("删除成功");
}
jar包虽好，但是要注意列名和属性名噢！时间够的话还是自己写会更方便，自己也更熟悉。
事务与回滚
某个操作会带动其他操作一起进行，但是只要其中一个操作失败，就需要回滚到最初的状态，这系列操作称为一个事务 ，例如转账：一个人付了钱，但是另一个人没有收到钱，此时应该回滚到最初的状态。来写个项目例子理解一下我们的事务：
1、首先我这里创建了两个表格一个学生，一个成绩，由于连表查询需要建立一个新表，而java中是不会去自动建表的，因此我们再来一个视图类，来展示连表查询的结果，并且创建了对应的实体包和类：
@Setter
@Getter
@NoArgsConstructor
@ToString
public class Student implements Serializable {
private static final long serialVersionUID = -6689308536691134283L;
private Integer stuId;
private String stuName;
private String stuSex;
private Date stuBirth;
private String stuDepartment;
private String stuAddress;
public Student(String stuName, String stuSex, Date stuBirth, String stuDepartment, String stuAddress) {
this.stuName = stuName;
this.stuSex = stuSex;
this.stuBirth = stuBirth;
this.stuDepartment = stuDepartment;
this.stuAddress = stuAddress;
}
}
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Score implements Serializable {
private static final long serialVersionUID = -3913875615842044280L;
private Integer scoId;
private Integer stuId;
private String scoName;
private Integer scoGrade;
}
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class StudentVO implements Serializable {
private static final long serialVersionUID = 1744279968004864793L;
private Integer stuId;
private String stuName;
private String stuSex;
private String stuDepartment;
private String scoName;
private Integer scoGrade;
}
2、创建菜单控制层的包和下面的类：
public class StudentAction {
private static Scanner scanner = new Scanner(System.in);
private static StudentService studentService=new StudentServiceImpl();
public static void startMenu() {
boolean b = true;
while (b) {
System.out.println("********学生管理系统********");
System.out.println("1、添加学生信息");
System.out.println("2、删除学生信息");
System.out.println("3、修改学生信息");
System.out.println("4、查询学生信息");
System.out.println("0、退出系统");
int choice = scanner.nextInt();
switch (choice) {
case 1:studentService.add(scanner);
break;
case 2:studentService.delete(scanner);
break;
case 3:studentService.update(scanner);
break;
case 4:studentService.select();
break;
case 0:
b = false;
System.out.println("系统已退出");
break;
}
}
}
}
3、上面我把连接数据库，并且增删改查等操作放进了我们工具类包下面：
跟sql有关的工具类：
public class SqlUtil {
public static Connection getConnections() {
Properties p = new Properties();
try {
p.load(SqlUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
Class.forName(p.getProperty("driver"));
return DriverManager.getConnection(p.getProperty("url"), p.getProperty("user"), p.getProperty("password"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void main(String[] args) {
System.out.println(getConnections());
}
public static boolean isConnection(Connection connection) {
try {
if (connection.isClosed()) return false;
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public static void close(Connection connection, Statement statement) {
try {
if (connection != null && !connection.isClosed())
connection.isClosed();
if (statement != null && !statement.isClosed())
statement.isClosed();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
close(connection, statement);
try {
if (resultSet != null && !resultSet.isClosed())
resultSet.isClosed();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int update(String sql, Object... args) {
int i = 0;
Connection connections = getConnections();
if (!isConnection(connections)) return -1;
PreparedStatement statement = null;
try {
statement = connections.prepareStatement(sql);
if (args == null || args.length == 0) return -1;
for (int i1 = 0; i1 < args.length; i1++) {
statement.setObject((i1 + 1), args[i1]);
}
i = statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(connections, statement);
}
return i;
}
public static int update(Connection connection, String sql, Object... args) {
int i = 0;
if (!isConnection(connection)) return -1;
try {
PreparedStatement statement = connection.prepareStatement(sql);
if (args == null || args.length == 0) return -1;
for (int i1 = 0; i1 < args.length; i1++) {
statement.setObject((i1 + 1), args[i1]);
}
i = statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
public static <T> List<T> select(String sql, Class t, Object... args) {
Connection connection = getConnections();
if (!isConnection(connection)) return null;
List<T> list = new ArrayList<>(10);
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sql);
//也可以根据某一个参数来进行查找
if (args != null && args.length != 0) {
for (int i = 0; i < args.length; i++) {
statement.setObject((i + 1), args[i]);
}
}
resultSet = statement.executeQuery();
while (resultSet.next()) {
Object tObject = t.newInstance();
//得到每一行的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//得到表格的列数
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
//获得每列列名
String columnLabel = metaData.getColumnLabel((i + 1));
//获得每列类的存放路径
String columnClassName = metaData.getColumnClassName((i + 1));
//获得每列值
Object valueObject = resultSet.getObject(columnLabel);
//获取方法名
String methodName = getMethodName(columnLabel);
//获取方法的返回值类型
Class methodReturn = getClass(columnClassName);
//注入值
setValue(t, valueObject, tObject, methodName, methodReturn);
}
list.add((T) tObject);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
close(connection, statement, resultSet);
}
return list;
}
public static <T> T selectObject(String sql, Class t, Object... args) {
Connection connection = getConnections();
if (!isConnection(connection)) return null;
PreparedStatement statement = null;
ResultSet resultSet = null;
Object obj = null;
try {
statement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
statement.setObject((i + 1), args[i]);
}
resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
obj = t.newInstance();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
for (int i = 0; i < columnCount; i++) {
String methodName = getMethodName(metaData.getColumnLabel((i + 1)));
Class aClass = getClass(metaData.getColumnClassName((i + 1)));
Object object = resultSet.getObject((i + 1));
setValue(t, object, obj, methodName, aClass);
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
close(connection, statement, resultSet);
}
return (T) obj;
}
/**
* 注入值
*
* @param t
* @param valueObject
* @param tObejct
* @param methodName
* @param methodReturn
*/
private static void setValue(Class t, Object valueObject, Object tObejct, String methodName, Class methodReturn) {
try {
Method method = t.getMethod(methodName, methodReturn);
method.setAccessible(true);
method.invoke(tObejct, valueObject);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
/**
* 根据类型存放路径得到类型
*
* @param classPath
* @return
*/
private static Class getClass(String classPath) {
if (classPath.equals("java.sql.Date")) {
classPath = "java.util.Date";
}
try {
return Class.forName(classPath);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
/**
* 根据列名来获取方法名（列名有下划线，利用下划线分隔，然后首字母大写）
*
* @param label
* @return
*/
private static String getMethodName(String label) {
StringBuilder builder = new StringBuilder("set");
String[] split = label.split("_");
for (String s : split) {
builder.append(Character.toUpperCase(s.charAt(0)));
builder.append(s.substring(1));
}
return builder.toString();
}
/**
* 利用DBUtils工具jar包来进行增删改
*
* @param sql
* @param args
* @return
*/
public static int updateByJar(String sql, Object... args) {
int i = 0;
Connection connection = getConnections();
if (!isConnection(connection)) return -1;
try {
i = new QueryRunner().update(connection, sql, args);
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
/**
* 利用jar包来进行查询全部结果集
*
* @param sql
* @param t
* @param args
* @param <T>
* @return 结果集
*/
public static <T> List<T> queryReturnList(String sql, Class<T> t, Object... args) {
Connection connections = getConnections();
if (!isConnection(connections)) return null;
List<T> list = new ArrayList<>(10);
try {
list = new QueryRunner().query(sql, new BeanListHandler<>(t), args);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 根据条件查询单个对象
*
* @param sql
* @param t
* @param args
* @param <T>
* @return 对象
*/
public static <T> T queryReturnObject(String sql, Class<T> t, Object... args) {
Connection connections = getConnections();
if (!isConnection(connections)) return null;
T t1 = null;
try {
t1 = new QueryRunner().query(connections, sql, new BeanHandler<>(t), args);
} catch (SQLException e) {
e.printStackTrace();
}
return t1;
}
}
跟日期有关的工具类：
public class DateForM {
public static Date strToDate(String str){
DateFormat format=new SimpleDateFormat("yyyy");
try {
return format.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
}
4、创建数据持久层（跟数据库有关的全部操作交给这一层），设计接口和完成实现类：
public interface StudentDao {
/**
* 新增
* @param student
* @return
*/
int add(Student student);
/**
* 删除
* @param id
* @return
*/
int delete(int id);
/**
* 事务需要的删除
* @param id
* @param connection
* @return
*/
int delete(int id, Connection connection);
/**
* 修改
* @param student
* @return
*/
int update(Student student);
/**
* 连表查询
* @return
*/
List<StudentVO> selectAll();
/**
* 根据学号得到单个对象
* @param id
* @return
*/
Student getStudent(int id);
}
public class StudentDaoImpl implements StudentDao {
@Override
public int add(Student student) {
String sql="insert into tb_student values(null,?,?,?,?,?)";
return SqlUtil.update(sql,student.getStuName(),student.getStuSex(),student.getStuBirth(),
student.getStuDepartment(),student.getStuAddress());
}
@Override
public int delete(int id) {
String sql="delete from tb_student where stu_id=?";
return SqlUtil.update(sql,id);
}
public int delete(int id, Connection connection) {
String sql="delete from tb_student where stu_id=?";
return SqlUtil.update(connection,sql,id);
}
@Override
public int update(Student student) {
String sql="update tb_student set stu_name=? where stu_id=?";
return SqlUtil.update(sql,student.getStuName(),student.getStuId());
}
@Override
public List<StudentVO> selectAll() {
String sql="select s1.stu_id,s1.stu_name,s1.stu_sex,s1.stu_department, " +
"s2.sco_name,s2.sco_grade from tb_student s1 left join tb_score s2 on s1.stu_id=s2.stu_id";
List<StudentVO> select = SqlUtil.select(sql, StudentVO.class);
return select;
}
@Override
public Student getStudent(int id){
String sql="select * from tb_student where stu_id=?";
return SqlUtil.selectObject(sql,Student.class,id);
}
}
public interface ScoreDao {
/**
* 在同一个连接对象下面删除分数
* @param id
* @param connection
* @return
*/
int deleteScore(int id, Connection connection);
/**
* 查询学号对应学生的全部分数
* @param id
* @return
*/
List<Score> selectScore(int id);
}
public class ScoreDaoImpl implements ScoreDao {
/**删学生之前要删成绩
* @param id
* @param connection
* @return
*/
@Override
public int deleteScore(int id, Connection connection) {
String sql="delete from tb_score where stu_id=?";
return SqlUtil.update(connection,sql,id);
}
@Override
public List<Score> selectScore(int id) {
String sql="select * from tb_score where stu_id=?";
List<Score> select = SqlUtil.select(sql, Score.class, id);
return select;
}
}
5、对于数据持久层得到的数据我们拿到业务层来进行操作，这里我们操作的时候要注意删除学生之前必须判断他是否有考试成绩，如果有，先要删除分数，并且两者有一个失败，这个业务就不能成功，这就是一个事务，分析完之后设计业务层接口和是实现类；
public interface StudentService {
/**
* 新增
* @param scanner
*/
void add(Scanner scanner);
/**
* 查询
*/
void select();
/**
* 删除
* @param scanner
*/
void delete(Scanner scanner);
/**
* 修改
* @param scanner
*/
void update(Scanner scanner);
}
public class StudentServiceImpl implements StudentService {
private StudentDao dao = new StudentDaoImpl();
private ScoreDao scoreDao = new ScoreDaoImpl();
@Override
public void add(Scanner scanner) {
System.out.println("请输入需要添加的学生信息：姓名、性别、出生年份、学院、家庭地址");
Student student = new Student(scanner.next(), String.valueOf(scanner.next().charAt(0)),
DateForM.strToDate(scanner.next().substring(0, 4))
, scanner.next(), scanner.next());
int add = dao.add(student);
if (add > 0) System.out.println("添加成功！");
else System.out.println("添加失败");
}
@Override
public void select() {
System.out.println("学生信息如下：");
List<StudentVO> studentVOS = dao.selectAll();
studentVOS.forEach(System.out::println);
}
@Override
public void delete(Scanner scanner) {
System.out.println("请输入需要删除的学生编号：");
int id = scanner.nextInt();
List<Score> scores = scoreDao.selectScore(id);
if (!scores.isEmpty()) {
System.out.println("该学生存在成绩信息，如若删除其成绩也会与之一起删除，确认要删除吗？[y/n]");
String answer = scanner.next().toLowerCase();
if ("y".equals(answer)) {
Connection connection = SqlUtil.getConnections();
if (!SqlUtil.isConnection(connection)) return;
try {//将自动提交改为false
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
int i = scoreDao.deleteScore(id, connection);
if (i > 0) {
System.out.println("删除分数成功！");
int delete = dao.delete(id, connection);
if (delete > 0) {
System.out.println("删除学生成功！");
try {
DbUtils.commitAndClose(connection);
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("删除失败！");
try {
DbUtils.rollback(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
} else {
System.out.println("删除失败！");
try {
DbUtils.rollback(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
} else System.out.println("删除失败！已返回");
} else {
int delete = dao.delete(id);
if (delete > 0) System.out.println("删除成功！");
else System.out.println("删除失败！");
}
}
@Override
public void update(Scanner scanner) {
Student student;
System.out.println("请输入需要修改的学生编号：");
int id = scanner.nextInt();
System.out.println(dao.getStudent(id));
student = dao.getStudent(id);
if (student == null) {
System.out.println("修改失败！该学生不存在！");
return;
}
int update = dao.update(student);
if (update > 0) System.out.println("修改成功！");
else System.out.println("修改失败！");
}
}
最后一步、我们在测试类通过控制层来启动：
public class TestDemo {
public static void main(String[] args) {
StudentAction.startMenu();
}
}
