java jdbc连接数据库 查询user表

浏览 41 次     时间 2021-08-26 21:57:34     作者 有勇气的牛排    标签 Java

1 数据库

  • 数据库:student

  • 表:users

mysql> select * from users;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | tom  | 123      |
+----+------+----------+
1 row in set (0.07 sec)

2 maven依赖

<dependencies>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.8</version>
    </dependency>
</dependencies>

3 jdbc连接案例

package net920vip;

import java.sql.*;

public class JDBCTest {

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // String url = "jdbc:mysql://localhost:3308/test?useUnicode=true&characterEncoding=utf-8";
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
            stmt = conn.createStatement();
            String sql = "select * from users";
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String password = rs.getString(3);
                System.out.println("id: " + id + ",name: " + name + ",password: " + password);
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

输出:

id: 1,name: tom,password: 123

4 指定用户名查询

Class.forName("com.mysql.jdbc.Driver");
// String url = "jdbc:mysql://localhost:3308/test?useUnicode=true&characterEncoding=utf-8";
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");


String sql = "select * from users where username like ? and id = ?";
pstm = conn.prepareStatement(sql);
pstm.setString(1, "%root%");
pstm.setInt(2, 1);

rs = pstm.executeQuery();

if (rs.next()) {
    int id = rs.getInt(1);
    String username = rs.getString(2);
    System.out.println("id:" + id + ", username:" + username);
}
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。