Java查询数据库

创建数据库

创建 user 数据库

　　

创建 teacher 数据库

　　

teacher表的user_id列与user表的id列建立一对多连接，user_id作为外键。

Java编程查询数据库

向user数据表中添加数据

    /**
     * 添加数据
     */
    @Test
    public void addData() {
        Connection connection = null;
        PreparedStatement pstmt =null;
        try {
            connection = JDBCUtils_V3.getConnection();
            String sql = "insert into user values(null,?,?)";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, "wangxuan");
            pstmt.setString(2, "741852");
            int row = pstmt.executeUpdate();
            if (row>0) {
                System.out.println("数据添加成功！");
            }else {
                System.out.println("数据添加失败！");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtils_V3.release(connection, pstmt, null);
        }
    }

　　

按照条件查询user数据库数据

    /**
     * 按照条件查询数据
     */
    @Test
    public void selectTest() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        try {
            conn = JDBCUtils_V3.getConnection();
            String sql = "select * from user where password = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "123456");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1)+"----"+rs.getString(2)+"---"+rs.getString(3));
            }
//            System.out.println(rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtils_V3.release(conn, pstmt, rs);
        }
    }

 　　

一对多查询/根据主表user查询从表teacher数据

    /**
     * 一对多查询
     * 根据主表查询从表
     */
    @Test
    public void selectOnetoMore() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils_V3.getConnection();
//            String sql = "select * from teacher where user_id = (select id from user where username =?) ";
            String sql = "select * from user,teacher where user.id = teacher.user_id ";
            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1, "wangxuan");
            rs = pstmt.executeQuery();
            while (rs.next()) {
//                System.out.println(rs.getString(1)+"----"+rs.getString(2)+"---"+rs.getString(3)+"---"+rs.getString(4));
                System.out.println(rs.getString(1)+"----"+rs.getString(2)+"---"+rs.getString(3)+"---"+rs.getString(4)+"----"+rs.getString(5)+"----"+rs.getString(6)+"----"+rs.getString(7));
            }
            System.out.println("查询完成");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtils_V3.release(conn, pstmt, rs);
        }
    }

 　　

一对多查询/根据从表查询主表

    /**
     * 一对多查询
     * 根据从表查询主表数据
     */
    @Test
    public void selectMoretoOne() {
        Connection connection = null;
        PreparedStatement pstmtPreparedStatement = null;
        ResultSet rSet =null;
        try {
            connection = JDBCUtils_V3.getConnection();
            String sql = "select * from user where id = (select user_id from teacher where teacher=?)";
            pstmtPreparedStatement = connection.prepareStatement(sql);
            pstmtPreparedStatement.setString(1, "钱田");
            rSet = pstmtPreparedStatement.executeQuery();
            while (rSet.next()) {
                System.out.println(rSet.getString(1)+"----"+rSet.getString(2)+"---"+rSet.getString(3));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtils_V3.release(connection, pstmtPreparedStatement, rSet);
        }
    }
}

 　　

 

项目代码：https://github.com/wjw1014/JavaMysqlStudy/tree/master/ConnSQL （小白操作，仅供参考！）