JDBC连接数据库操作（三）

利用前两篇的知识，写一个综合的小案例，实现对数据库的增删查改。

表的结构

数据库名为day12

表名为person
    表有两列，列名分别是id和name

代码

import java.sql.ResultSet;
import java.util.Scanner;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class DatabaseDemo {
    public static void main(String[] args) throws Exception {
        boolean flag = true;
        int choose;
        Scanner sc = new Scanner(System.in);
        Connection con = JDBCUtils.getCon();
        String sql;
        String values;
        PreparedStatement psmt = null;
        int id;
        int line;

        do {
            System.out.println("==================数据库操作==================");
            System.out.println(" 1查询    2插入     3更改     4删除      0退出  ");
            System.out.println("============================================");
            choose = sc.nextInt();
            switch (choose) {
            case 2:
                sql = "insert into person(name) values(?)";
                psmt = (PreparedStatement) con.prepareStatement(sql);
                System.out.println("输入姓名：");
                values = sc.next();
                psmt.setString(1, values);
                line = psmt.executeUpdate();
                System.out.println("新添加的记录："+line);
                break;

            case 1:
                sql = "select * from person";
                psmt = (PreparedStatement) con.prepareStatement(sql);
                ResultSet rs = psmt.executeQuery();
                System.out.println("id\tname");
                while (rs.next()) {
                    int idc = rs.getInt("id");
                    String name = rs.getString("name");
                    System.out.println(idc+"\t"+name);
                }
                rs.close();
                break;

            case 3:
                sql = "update person set name=? where id=?";
                psmt = (PreparedStatement) con.prepareStatement(sql);
                System.out.println("请输入新的name：");
                values = sc.next();
                System.out.println("请输入id号：");
                id = sc.nextInt();
                psmt.setString(1, values);
                psmt.setInt(2, id);
                line = psmt.executeUpdate();
                System.out.println("新添加的记录："+line);
                break;

            case 4:
                sql = "delete from person where id=?";
                psmt = (PreparedStatement) con.prepareStatement(sql);
                System.out.println("请输入id号：");
                id = sc.nextInt();
                psmt.setInt(1, id);
                line = psmt.executeUpdate();
                System.out.println("新添加的记录："+line);
                break;

            case 0:
                flag = false;
                sc.close();
                psmt.close();
                con.close();
                break;

            default:
                System.out.println("输入值无效");
                break;
            }
        } while (flag);
    }
}

JDBC工具类

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import com.mysql.jdbc.Connection;

public class JDBCUtils {
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;

    // 通过配置文件来获取用户名密码等连接数据库的信息，是一种常用的手段
    static {
        try {
            // Properties可保存在流中或从流中加载,属性列表中每个键及其对应值都是一个字符串
            Properties prop = new Properties();

            // 使用IO流获得配置文件
            InputStream in = new FileInputStream("D:\\setfile.txt");

            // 将文件加载到Properties
            prop.load(in);

            // 通过键来取对应的值
            DRIVER = prop.getProperty("driver");
            URL = prop.getProperty("url");
            USER = prop.getProperty("user");
            PASSWORD = prop.getProperty("password");

            in.close(); //关闭IO流
        } catch (Exception e) {
            System.out.println("获取配置文件失败");
        }
    }

    public static Connection getCon() {
        try {
            // 驱动注册
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection con = null;
        try {
            con = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return con;
    }
}

配置文件

文件的位置在D盘的根目录下，里面是以键值对的形式存储的，方便通过使用代码来一一取出。

等号前面的是键，等号后面的是对应的值