Felix021

import java.sql.*;

public class Jdbc11 {
    public static void main (String args[]) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Statement stmt = null;
            String url     = "jdbc:mysql://localhost:3306/test";
            String dbuser  = "root";
            String dbpass  = "123456";
            String dbname  = "felix021";
            String tblname = "users";

            Connection con = DriverManager.getConnection(url, dbuser, dbpass);
            stmt = con.createStatement();
            System.out.println("URL: " + url);
            System.out.println("Connection: " + con);

            //建库
            stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbname);
            stmt.executeUpdate("USE " + dbname);

            //建表
            stmt.executeUpdate("DROP TABLE IF EXISTS " + tblname);
            stmt.executeUpdate(
                    "CREATE TABLE " + tblname + "(\n" +
                    "  `id` INT PRIMARY KEY AUTO_INCREMENT, \n" +
                    "  `name` CHAR(20) NOT NULL, \n" +
                    "  `description` varchar(255) DEFAULT NULL\n" +
                    ")"
                    );

            //插入
            int count = stmt.executeUpdate(
                    "INSERT INTO " + tblname + "\n" +
                    "(`id`, `name`, `description`) VALUES \n" +
                    "(NULL, 'a', 'ooxx'), \n" +
                    "(NULL, 'b', NULL), \n" +
                    "(NULL, 'c', 'haha'), \n" +
                    "(NULL, 'd', 'hoho')"
                    );
            System.out.println("Inserted " + count + " rows");

            //statement for resultset
            stmt = con.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            //查询
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tblname);
            System.out.println("All results are listed below:");
            while (rs.next()) {
                int    id           = rs.getInt("id");
                String name         = rs.getString("name");
                String description  = rs.getString("description");
                System.out.println(
                        "id=" + id + ", " +
                        "the name is " + name + ", " +
                        description
                        );
            }

            //删表
            stmt.executeUpdate("DROP TABLE " + tblname);

            //删库
            stmt.executeUpdate("DROP DATABASE " + dbname);
        }
        catch (SQLException sqlE) {
            System.out.println("SQL Error: " + sqlE);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}