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​​ （小白操作，仅供参考！）

【版权声明】本博文著作权归作者所有，任何形式的转载都请联系作者获取授权并注明出处！

【重要说明】本文为本人的学习记录，论点和观点仅代表个人而不代表当时技术的真理，目的是自我学习和有幸成为可以向他人分享的经验，因此有错误会虚心接受改正，但不代表此刻博文无误！

​

【Gitee地址】秦浩铖：​​https://gitee.com/wjw1014​​