//java连接数据库，对数据库进行增删改查的操作
//main函数调用增删改查的方法
//正删改查的方法调用具体操作
//每个操作又调用工具类进行链接数据库
import java.util.List;
import java.util.Scanner;
public class TestDB {
private static Scanner scanner = new Scanner(System.in);
private static int id;
private static String name;
private static String password;
public static void main(String[] args) {
fun_add();//测试添加
fun_delete();//测试删除
fun_find();//查找所有
fun_alert();// 修改
}
private static void fun_alert() {
fun_find();// 查找所有
System.out.println();
System.out.println("<<修改用户信息>>");
System.out.print("请输入用户id-->");
id = scanner.nextInt();
System.out.print("请输入用户名-->");
name = scanner.next();
System.out.print("请输入用户密码-->");
password = scanner.next();
Person person = new Person(id, name, password);
boolean alert = CtrlDB.alert(person);
if (alert) {
System.out.println("修改成功！");
} else {
System.out.println("修改失败！");
}
}
private static void fun_find() {
System.out.println("所有用户信息如下-->>");
List<Person> seek = CtrlDB.seek();
for (Person person : seek) {
System.out.println(person.getId() + ":" + person.getName() + "," + person.getPassword());
}
}
private static void fun_delete() {
fun_find();// 查找所有
System.out.println("<<删除用户>>");
System.out.print("请输入用户id--");
id = scanner.nextInt();
boolean boo = CtrlDB.delete(id);
if (boo) {
System.out.println("删除成功！");
} else {
System.out.println("删除失败！");
}
}
public static void fun_add() {
System.out.println("<<添加用户>>");
System.out.print("请输入用户名--");
name = scanner.next();
System.out.print("请输入密码--");
password = scanner.next();
Person person = new Person(name, password);
boolean b = CtrlDB.add(person);
if (b) {
System.out.println("添加成功！");
} else {
System.out.println("添加失败！");
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
//com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
public class CtrlDB {
public static boolean alert(Person person) {
int id = person.getId();
String name = person.getName();
String password = person.getPassword();
Connection connection = DBUtiles_2.getConnection();
PreparedStatement pStatement = null;
// Statement statement = null;
String sql = "UPDATE person SET name=?, password=? WHERE id=?";
// String sql = "UPDATE person SET name=" + name + ", password=" +
// password + " WHERE id=" + id;
try {
pStatement = connection.prepareStatement(sql);
pStatement.setObject(1, name);
pStatement.setObject(2, password);
pStatement.setObject(3, id);
boolean execute = pStatement.execute();
// statement = connection.createStatement();
// boolean execute = statement.execute(sql);
if (!execute) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtiles_2.close(connection, pStatement);
// DBUtiles_2.close(connection, statement);
}
return false;
}
public static List<Person> seek() {
List<Person> list = new ArrayList<Person>();
Person person;
int id;
String name;
String password;
Connection connection = DBUtiles_2.getConnection();
PreparedStatement pStatement = null;
String sql = "SELECT * FROM person";
try {
pStatement = connection.prepareStatement(sql);
ResultSet executeQuery = pStatement.executeQuery();
while (executeQuery.next()) {
id = executeQuery.getInt(1);
name = executeQuery.getString(2);
password = executeQuery.getString(3);
person = new Person(id, name, password);
list.add(person);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtiles_2.close(connection, pStatement);
}
return list;
}
public static boolean add(Person person) {
String name = person.getName();
String password = person.getPassword();
Connection connection = DBUtiles_2.getConnection();
PreparedStatement pStatement = null;
String sql = "INSERT INTO person (name,password)VALUES (?,?)";
try {
pStatement = connection.prepareStatement(sql);
pStatement.setObject(1, name);
pStatement.setObject(2, password);
boolean execute = pStatement.execute();
if (execute) {
return false;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtiles_2.close(connection, pStatement);// 关闭资源
}
return true;
}
public static boolean delete(int id) {
Connection connection = DBUtiles_2.getConnection();// 获取连接
// 写sql语句
String sql = "DELETE FROM person WHERE id=" + id;
Statement statement = null;
try {
// PreparedStatement pStatement = connection.prepareStatement(sql);
statement = connection.createStatement();
boolean execute = statement.execute(sql);
if (!execute) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtiles_2.close(connection, statement);
}
return false;
}
}
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
* 加载properties配置文件
* IO读取文件,键值对存储到集合
* 从集合中以键值对方式获取数据库的连接信息,完成数据库的连接
*/
public class DBUtiles_2 {
private static Connection connection;
private static String driverClass;
private static String url;
private static String username;
private static String password;
static{
try {
readConfig();//读配置文件信息
Class.forName(driverClass);
//获取连接
connection = DriverManager.getConnection(url, username, password);//"jdbc:mysql://localhost:3306/mydatabase"
} catch (Exception e) {
e.printStackTrace();
}
}
private static void readConfig() throws IOException {
//使用类的加载器
InputStream iStream = DBUtiles_2.class.getClassLoader().getResourceAsStream("connect.properties");
//创建properties类对象
Properties properties = new Properties();
properties.load(iStream);
driverClass = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}
public static Connection getConnection(){
return connection;
}
public static void close(Connection connection,Statement statement,ResultSet rSet){
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rSet != null){
try {
rSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection,Statement statement){
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
