java jdbc连接数据库查询user表

有勇气的牛排 1199 Java 2021-08-26 21:57:34

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);
}

<h2><a id="1__0"></a>1 数据库</h2> <ul> <li> 数据库：student </li> <li> 表：users </li> </ul> <pre><div class="hljs"><code class="lang-shell">mysql> select * from users; +----+------+----------+ | id | name | password | +----+------+----------+ | 1 | tom | 123 | +----+------+----------+ 1 row in set (0.07 sec) </code></div></pre> <h2><a id="2_maven_14"></a>2 maven依赖</h2> <pre><div class="hljs"><code class="lang-xml"><dependencies>  <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.8</version> </dependency> </dependencies> </code></div></pre> <h2><a id="3_jdbc_27"></a>3 jdbc连接案例</h2> <pre><div class="hljs"><code class="lang-java">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(); } } } } } </code></div></pre> 输出： <pre><code class="lang-">id: 1,name: tom,password: 123 </code></pre> <h2><a id="4__88"></a>4 指定用户名查询</h2> <pre><div class="hljs"><code class="lang-java">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); } </code></div></pre>