先创建properties文件要放在src下面不然后面代码类加载器中获取地址会获取不到
jdbc.properties代码
user = root
url = jdbc:mysql:///mydb1
password = root
driver =com.mysql.jdbc.Driver
工具类DBUtils.java代码
package Day2;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class DBUtils {
//静态的最早运行
private static String driver;
private static String url;
private static String user;
private static String password;
static {
try {
//1.创建Properties集合类
Properties pro = new Properties();
//2.加载文件
//pro.load( new FileReader("D:\\IdeaProjects\\work2\\src\\Day2\\jdbc.properties"));
//2.方法二创建类加载器
ClassLoader classLoader = DBUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
pro.load(new FileReader(path));
//3.获取数据
url=pro.getProperty("url");
driver=pro.getProperty("driver");
user=pro.getProperty("user");
password=pro.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
System.out.println("io异常");
} catch (ClassNotFoundException e) {
System.out.println("加载驱动异常");
}
}
public static Connection getConn() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void close(PreparedStatement pst, Connection conn) {
if( pst != null){
try {
pst.close();
} catch (SQLException e) {
System.out.println("stat关闭异常");
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("conn关闭异常");
}
}
}
public static void close(PreparedStatement pst, Connection conn,ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
System.out.println("rs关闭异常");
}
}if (pst != null){
try {
pst.close();
} catch (SQLException e) {
System.out.println("stat关闭异常");
}
}if (conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("conn关闭异常");
}
}
}public static void close(Statement stat, Connection conn,ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
System.out.println("rs关闭异常");
}
}if (stat != null){
try {
stat.close();
} catch (SQLException e) {
System.out.println("stat关闭异常");
}
}if (conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("conn关闭异常");
}
}
}
//用来检验跟数据连接是否成功
public static void main(String[] args) {
try {
Connection conn = DBUtils.getConn();
if (conn != null) {
System.out.println("连接成功");
}
} catch (SQLException e) {
System.out.println("连接失败");
}
}
}
主代码Text.java
package Day2;
import java.sql.*;
import java.util.Scanner;
public class Text {
public static void main(String[] args) throws SQLException {
DBUtils.getConn();
boolean flg =true;
while (flg) {
System.out.println("用户管理系统");
System.out.println("1.添加用户信息");
System.out.println("2.查询用户信息");
System.out.println("3.修改用户密码");
System.out.println("4.删除用户");
System.out.println("5.打印所有信息");
System.out.println("6.退出");
Scanner sc = new Scanner(System.in);
switch (sc.nextInt()){
case 1:{Insert();break;}
case 2:{chaxun();break;}
case 3:{change();break;}
case 4:{delect();break;}
case 5:{dayin();break;}
case 6:{flg=false;break;}
default:
System.out.println("请输入1-6的数");
break;
}
}
}
public static void Insert() throws SQLException {
Connection conn = DBUtils.getConn();
Scanner sc =new Scanner(System.in);
System.out.println("请输入id");
int id = sc.nextInt();
System.out.println("请输入名字");
String name =sc.next();
System.out.println("请输入密码");
String password = sc.next();
String sql1 ="select id from info where id=?";
PreparedStatement pst1=conn.prepareStatement(sql1);
pst1.setInt(1,id);
ResultSet rs=pst1.executeQuery();
if (!rs.next()){
String sql = "insert into info values (?,?,?)";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setInt(1,id);
pst.setString(2,name);
pst.setString(3,password);
pst.executeUpdate();
System.out.println("添加成功");
DBUtils.close(pst,conn,rs);}
else System.out.println("id已占用请重新输入");
}
public static void chaxun() throws SQLException {
Connection conn = DBUtils.getConn();
Scanner sc =new Scanner(System.in);
System.out.println("请输入要查询用户信息的id");
String id =sc.nextLine();
String sql ="select * from info where id =?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,id);
ResultSet rs=pst.executeQuery();
while (rs.next()){
System.out.print("name:"+rs.getString("user")+" ");
System.out.println("password:"+rs.getInt("password"));
}
if (rs.next()!=true) System.out.println("输入id有误请重新输入");
DBUtils.close(pst,conn,rs);
}
public static void change() throws SQLException {
Connection conn = DBUtils.getConn();
Scanner sc =new Scanner(System.in);
System.out.println("请输入要修改密码用户的id");
String id =sc.nextLine();
System.out.println("请输入新密码");
String password =sc.nextLine();
String sql = "update info set password =? where id =?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,password);
pst.setString(2,id);
pst.executeUpdate();
System.out.println("修改成功");
DBUtils.close(pst,conn);
}
public static void delect() throws SQLException {
Connection conn = DBUtils.getConn();
Scanner sc =new Scanner(System.in);
System.out.println("请输入要删除的用户的id");
String id =sc.nextLine();
String sql = "delete from info where id=?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,id);
pst.executeUpdate();
System.out.println("删除成功");
DBUtils.close(pst,conn);
}
public static void dayin() throws SQLException {
Connection conn = DBUtils.getConn();
String sql = "select * from info";
Statement sta =conn.createStatement();
ResultSet rs=sta.executeQuery(sql);
while (rs.next()){
System.out.print("id"+rs.getInt("id")+" ");
System.out.print("username"+rs.getString("user")+" ");
System.out.println("password"+rs.getInt("password"));
}
DBUtils.close(sta,conn,rs);
}
}