JDBC的使用方式

JDBC 概述

JDBC TM 是一种 应用编程接口，它制定处理表格数据和常见的关系型数据的标准框架。

数据库的基本概念：

数据库本质上是表的“智能”容器。

     表是由行组成的容器。

     行(概念上) 是由列组成的容器。

     列是具有名称、类型和值的单个数据项目。

通过JDBC 访问数据库

public class DbUTools {
    static Properties p =new Properties();
    
    static{
        InputStream is =DbUTools.class.getResourceAsStream("/jdbc.properties");
        try {
            p.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取连接
     */
    public static Connection getConnection() throws Exception{
        String url=p.getProperty("url");
        String driverClass=p.getProperty("driverClass");
        String userName =p.getProperty("userName");
        String password =p.getProperty("password");
        Class.forName(driverClass);
        Connection conn =DriverManager.getConnection(url, userName, password);
        return conn;
    }
}

Jdbc的增删改查方法

public class StudentManager {
Scanner sc =new Scanner(System.in);

//添加
    public void addStudent() throws Exception{
        System.out.println("请输入班级id:");
        int classId =sc.nextInt();
        
        System.out.println("请输入学生姓名:");
        String sname =sc.next();
        
        System.out.println("请输入学生年龄:");
        int sage =sc.nextInt();
        
        System.out.println("请输入学生性别:");
        String ssex =sc.next();
        
        String sql ="insert into student(cid,sname,sage,ssex) values (?,?,?,?)";
        
        Connection conn =Dbuit.getConnection();
        PreparedStatement ps =conn.prepareStatement(sql);
        
        ps.setInt(1, classId);
        ps.setString(2, sname);
        ps.setInt(3, sage);
        ps.setString(4, ssex);
        int index= ps.executeUpdate();
        if (index>0) {
            System.out.println("添加成功"+index+"行受影响");
        }
        
        ps.close();
        conn.close();
    }
    
//删除
    public void deleteStudent()throws Exception{
        System.out.println("请输入要删除的学生id:");
        int stuId =sc.nextInt();
        
        String sql ="delete from student where sid=?";
        Connection con =Dbuit.getConnection();
        
        PreparedStatement ps =con.prepareStatement(sql);
        ps.setInt(1, stuId);
        
        int index= ps.executeUpdate();
        if (index>0) {
            System.out.println("删除成功"+index+"行受影响");
        }
        
        ps.close();
        con.close();
}

//修改
    public void updateStudent()throws Exception{
        String sql ="update student set cid=?,sage=?,ssex=? where sid=?";
        
        System.out.println("请输入要修改的学生id");
        int stuId =sc.nextInt();
        
        System.out.println("请输入新的班级id:");
        int cid =sc.nextInt();
        
        System.out.println("请输入新的年龄:");
        int sage =sc.nextInt();
        
        System.out.println("请输入新的性别:");
        String ssex =sc.next();
        
        Connection conn =Dbuit.getConnection();
        PreparedStatement ps =conn.prepareStatement(sql);
        ps.setInt(1, cid);
        ps.setInt(2, sage);
        ps.setString(3, ssex);
        ps.setInt(4, stuId);
        
        int index =ps.executeUpdate();
        if (index>0) {
            System.out.println("修改成功"+index+"行受影响");
        }
        
        ps.close();
        conn.close();
}

//根据条件查询
    public void listStudent()throws Exception{
        System.out.println("请输入要查看的学生id");
        int sid =sc.nextInt();
        
        String sql ="select * from student where sid=?";
        
        Connection conn =Dbuit.getConnection();
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setInt(1, sid);
        
        ResultSet rSet =ps.executeQuery();  //执行sql语句
        ResultSetMetaData metaData = rSet.getMetaData();  //new一个getMetaData
        int columnCount = metaData.getColumnCount();  //获取表中的总列数
        
        for (int i = 1; i <= columnCount; i++) {
            System.out.print(metaData.getColumnLabel(i)+"\t");  //获取列名
        }
        System.out.println();
        while (rSet.next()) {
            for (int i = 1; i <=columnCount ; i++) {
                System.out.print(rSet.getString(i)+"\t");        
            }
            System.out.println();
        }
}

//查询所有
    public void list() throws Exception{
        String sql ="select * from student";
        
        Connection conn =Dbuit.getConnection();
        PreparedStatement ps=conn.prepareStatement(sql);

        
        ResultSet rSet =ps.executeQuery();  //执行sql语句
        ResultSetMetaData metaData = rSet.getMetaData();  //new一个getMetaData
        int columnCount = metaData.getColumnCount();  //获取表中的总列数
        
        for (int i = 1; i <= columnCount; i++) {
            System.out.print(metaData.getColumnLabel(i)+"\t");  //获取列名
        }
        System.out.println();
        while (rSet.next()) {
            for (int i = 1; i <=columnCount ; i++) {
                System.out.print(rSet.getString(i)+"\t");        
            }
            System.out.println();
        }
    }
}