Android Java JDBC H2 嵌入式数据库创建、读取、更新和删除示例

分手后的思念是犯贱 2023-10-01 13:13 47阅读 0赞

package com.example.androidsample;
    
    import androidx.appcompat.app.AppCompatActivity;
    
    import android.content.ContextWrapper;
    import android.os.Bundle;
    
    import java.io.File;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class MainActivity extends AppCompatActivity {
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            // JDBC driver name and database URL
            final String JDBC_DRIVER = "org.h2.Driver";
    
            ContextWrapper c = new ContextWrapper(this);
            String dbFile = c.getFilesDir().getAbsolutePath() + File.separator + "test";
            final String DB_URL = "jdbc:h2:" + dbFile;
    
            //  Database credentials
            final String USER = "sa";
            final String PASS = "";
    
    
            Connection conn = null;
            Statement stmt = null;
            try {
                // STEP 1: Register JDBC driver
                Class.forName(JDBC_DRIVER);
    
                //STEP 2: Open a connection
                System.out.println("Connecting to database...");
                conn = DriverManager.getConnection(DB_URL, USER, PASS);
    
                //STEP 3: Execute a query
                System.out.println("Creating table in given database...");
                stmt = conn.createStatement();
                String sql = "drop table users;";
                stmt.executeUpdate(sql);
                System.out.println("Drop table in given database...");
    
                sql = "create table users (\r\n" + "  id  int(3) primary key,\r\n" +
                        "  name varchar(20),\r\n" + "  email varchar(20),\r\n" + "  country varchar(20),\r\n" +
                        "  password varchar(20)\r\n" + "  );";
                stmt.executeUpdate(sql);
                System.out.println("Created table in given database...");
    
                // Step 2:Create a statement using connection object
                final String INSERT_USERS_SQL = "INSERT INTO users" +
                        "  (id, name, email, country, password) VALUES " +
                        " (?, ?, ?, ?, ?);";
    
                PreparedStatement preparedStatement = conn.prepareStatement(INSERT_USERS_SQL);
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, "Tony");
                preparedStatement.setString(3, "tony@gmail.com");
                preparedStatement.setString(4, "US");
                preparedStatement.setString(5, "secret");
    
                System.out.println(preparedStatement);
                // Step 3: Execute the query or update query
                preparedStatement.executeUpdate();
    
                final String QUERY = "select id,name,email,country,password from users where id =?";
                preparedStatement = conn.prepareStatement(QUERY);
                preparedStatement.setInt(1, 1);
                System.out.println(preparedStatement);
                // Step 3: Execute the query or update query
                ResultSet rs = preparedStatement.executeQuery();
    
                // Step 4: Process the ResultSet object.
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    String email = rs.getString("email");
                    String country = rs.getString("country");
                    String password = rs.getString("password");
                    System.out.println(id + "," + name + "," + email + "," + country + "," + password);
                }
                final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";
                preparedStatement = conn.prepareStatement(UPDATE_USERS_SQL);
                preparedStatement.setString(1, "Ram");
                preparedStatement.setInt(2, 1);
    
                // Step 3: Execute the query or update query
                preparedStatement.executeUpdate();
    
                System.out.println(preparedStatement);
                final String deleteTableSQL = "delete from users where id = 1";
                stmt.executeUpdate(deleteTableSQL);
                System.out.println(deleteTableSQL);
    
                // STEP 4: Clean-up environment
                stmt.close();
                conn.close();
            } catch (SQLException se) {
                //Handle errors for JDBC
                se.printStackTrace();
            } catch (Exception e) {
                //Handle errors for Class.forName
                e.printStackTrace();
            } finally {
                //finally block used to close resources
                try {
                    if (stmt != null) stmt.close();
                } catch (SQLException se2) {
                } // nothing we can do
                try {
                    if (conn != null) conn.close();
                } catch (SQLException se) {
                    se.printStackTrace();
                } //end finally try
            } //end try
            System.out.println("Goodbye!");
        }
    }

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAYWxsd2F5Mg_size_20_color_FFFFFF_t_70_g_se_x_16][]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAYWxsd2F5Mg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/e120b50cc9dd4aa182eb067667e01091.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAYWxsd2F5Mg==,size_20,color_FFFFFF,t_70,g_se,x_16