DBUtil工具类
①获取连接
②判断是否存在
③查找数据
④修改数据
⑤释放资源
package com.jd.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* 数据库工具类
*
* @author syy
*/
public class DBUtil {
/**
*
* 获取连接
*
* @author syy
*/
private static Connection getconnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = PropertiesUtil.getValue("jdbc.url");
String userName = PropertiesUtil.getValue("jdbc.userName");
String password = PropertiesUtil.getValue("jdbc.password");
connection = DriverManager.getConnection(url, userName , password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
/**
*
* 判断是否存在
*
* @author syy
*/
public static boolean exist(String sql, Object ...paras ) {
class RowMapper implements IRowMapper {
boolean state;
@Override
public void rowMapper(ResultSet resultSet) {
try {
state = resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
select(sql, rowMapper, paras);
return rowMapper.state;
}
/**
*
* 判断是否存在
*
* @author syy
*/
public static boolean exist(String sql) {
class RowMapper implements IRowMapper {
boolean state;
@Override
public void rowMapper(ResultSet resultSet) {
try {
state = resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
select(sql, rowMapper);
return rowMapper.state;
}
/**
*
* 查找数据
*
* @author syy
*/
public static void select(String sql, IRowMapper rowMapper) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getconnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);// 执行语句
rowMapper.rowMapper(resultSet); // 处理结果
} catch (Exception e) {
e.printStackTrace();
} finally {
close(resultSet, statement, connection);
}
}
/**
*
* 查找数据
*
* @author syy
*/
public static void select(String sql, IRowMapper rowMapper, Object... paras) { // 上转型对象,接口的实现类创建对象,接口不能创建对象。
Connection connection = null;
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
try {
connection = getconnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= paras.length; i++) {
preparedStatement.setObject(i, paras[i - 1]);
}
resultSet = preparedStatement.executeQuery(); //因为已经知道preparedStatement对象执行的语句是sql
rowMapper.rowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
} finally {
close(resultSet, preparedStatement, connection);
}
}
/**
* 修改数据
*
*
* @author syy
*/
public static void update(String ll, Object... paras) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getconnection();
preparedStatement = connection.prepareStatement(ll); // 创建语句
for (int i = 1; i <= paras.length; i++) {
preparedStatement.setObject(i, paras[i - 1]);
}
int result = preparedStatement.executeUpdate(); //不能添加ll
if (result > 0) {
System.out.println("成功");
} else {
System.out.println("失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(preparedStatement, connection);
}
}
/**
* 修改数据
*
*
* @author syy
*/
public static void update(String ll) {
Connection connection = null;
Statement statement = null;
try {
connection = getconnection();
statement = connection.createStatement(); //创建语句
int result = statement.executeUpdate(ll);
if (result > 0) {
System.out.println("成功");
} else {
System.out.println("失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(statement, connection);
}
}
/**
* 释放资源
*
*
* @author syy
*/
private static void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
close(statement, connection);
}
/**
* 释放资源
*
*
* @author syy
*/
private static void close(Statement statement, Connection connection) {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
简单的学生管理系统
实现功能
下面是代码:
package com.jd.util;
import java.sql.ResultSet;
public interface IRowMapper {
void rowMapper(ResultSet resultSet);
}
package jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import com.jd.util.DBUtil;
import com.jd.util.IRowMapper;
public class Main {
public static void main(String[] args) {
System.out.println("*********************************");
System.out.println("*\t\t\t\t*");
System.out.println("*\t欢迎使用学生信息管理系统\t*");
System.out.println("*\t\t\t\t*");
System.out.println("*********************************");
while (true) {
menu();
}
}
static void menu() {
System.out.println("1、添加学生信息");
System.out.println("2、删除学生信息");
System.out.println("3、修改学生信息");// 地址传递
System.out.println("4、查询学生信息");// name
System.out.println("请输入操作,以Enter键结束:");
Scanner scanner = new Scanner(System.in);
int option = scanner.nextInt();
class RowMapper implements IRowMapper {
@Override
public void rowMapper(ResultSet resultSet) {
try {
while (resultSet.next()) {
String id = resultSet.getString("id");
String name = resultSet.getString("user_name");
System.out.println(id);
System.out.println(name);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
switch (option) {
case 1: {
System.out.println("请输入新添加的学生学号");
String id = scanner.next();
if(DBUtil.exist("select * from user_info where id=?",id)) {
System.out.println("该学号已存在");
}else {
System.out.println("请输入姓名");
String name = scanner.next();
System.out.println("请输入密码");
String password = scanner.next();
DBUtil.update("insert into user_info (id,user_name,password)values(?,?,?)", id, name, password);
}
break;
}
case 2: {
System.out.println("删除学生信息");
System.out.println("请输入学生学号");
String id = scanner.next();
if(DBUtil.exist("select * from user_info where id=?",id)) {
DBUtil.update("delete from user_info where id=?",id);
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
break;
}
case 3: {
System.out.println("请输入修改的学生学号");
String id = scanner.next();
if(DBUtil.exist("select * from user_info where id=?",id)) {
System.out.println("请输入修改的密码");
String pa = scanner.next();
DBUtil.update("update user_info set password=? where id=?",pa,id);
}else {
System.out.println("学号不存在");
}
break;
}
case 4: {
System.out.println("请输入学生的学号");
String id = scanner.next();
if(DBUtil.exist("select * from user_info where id=?",id)) {
System.out.println("该学生的信息为");
RowMapper rowMapper =new RowMapper();
DBUtil.select("select * from user_info where id=?", rowMapper, id);
}else {
System.out.println("学号不存在");
}
break;
}
default:
System.out.println("I'm Sorry,there is not the " + option + " option,please try again.");
}
}
}
这次写这个系统花费了我大量时间,总算是非常有收获,体会到自己对于Java语法的不熟悉,这个创建一个接口,然后用内部类重写接口内的方法来完成“执行结果语句”。
用一个有名内部类来实现接口,如下面所示:
class RowMapper implements IRowMapper {
@Override
public void rowMapper(ResultSet resultSet) {
try {
while (resultSet.next()) {
String id = resultSet.getString("id");
String name = resultSet.getString("user_name");
System.out.println(id);
System.out.println(name);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
再用内部类创建对象,传入查询、修改等方法中,在这些方法中再通过对象调用方法,实现不同结果的输出。