安装mysql数据库,下载mybatis-3.2.7.jar包
一、创建数据库
package com.jdbc; import java.sql.*; //创建数据库 public class CreateDatabase { static final String JDBC_DRIVER="com.mysql.jdbc.Driver"; static final String DB_URL="jdbc:mysql://localhost/"; static final String USER="root"; static final String PASS="zhz0514"; public static void main(String[] args){ Connection conn = null; Statement stmt = null; try{ Class.forName ("com.mysql.jdbc.Driver"); System.out.println ("Connecting to database..."); conn = DriverManager.getConnection (DB_URL,USER,PASS); System.out.println ("Creating database..."); stmt = conn.createStatement (); String sql = "CREATE DATABASE jdbc_db"; stmt.executeUpdate (sql); System.out.println ("Database created successsfully..."); }catch (SQLException se){ se.printStackTrace (); }catch (Exception e){ e.printStackTrace (); }finally { try{ if (stmt!=null) stmt.close (); }catch (SQLException se2){ try{ if (conn!=null) conn.close (); }catch (SQLException se){ se.printStackTrace (); } } } System.out.println ("Goodbye"); } }
二、创建表
package com.jdbc; import org.omg.Messaging.SYNC_WITH_TRANSPORT; import java.sql.*; //创建表 public class CreateTable { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/jdbc_db"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main(String[] args){ Connection conn = null; Statement stmt = null; try { Class.forName ("com.mysql.jdbc.Driver"); System.out.println ("Connecting to a selected database..."); conn = DriverManager.getConnection (DB_URL,USER,PASS); System.out.println ("Connected database successfully..."); System.out.println ("Creating table in given database..."); stmt = conn.createStatement (); String sql = "CREATE TABLE student"+ "(id INTEGER not NULL,"+ "first VARCHAR(255),"+ "last VARCHAR(255),"+ "age INTEGER,"+ "PRIMARY KEY(id))"; stmt.executeUpdate (sql); System.out.println ("Created table in given database..."); }catch (SQLException se){ se.printStackTrace (); }catch (Exception e){ e.printStackTrace (); }finally { try { if (stmt!=null) conn.close (); }catch (SQLException se){ } try { if (conn!=null) conn.close (); }catch (SQLException se){ se.printStackTrace (); } } System.out.println ("Goodbye!"); } }
三、插入数据
import java.sql.*; //插入数据 public class InsertRecords { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/jdbc_db"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main(String[] args){ Connection conn = null; Statement stmt = null; try { Class.forName("com.mysql.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); //STEP 4: Execute a query System.out.println("Inserting records into the table..."); stmt = conn.createStatement(); String sql = "INSERT INTO student " + "VALUES (100, 'C++', 'Li', 18)"; stmt.executeUpdate(sql); sql = "INSERT INTO student " + "VALUES (101, 'Python', 'Py', 25)"; stmt.executeUpdate(sql); sql = "INSERT INTO student " + "VALUES (102, 'Ruby', 'Ru', 30)"; stmt.executeUpdate(sql); sql = "INSERT INTO student " + "VALUES(103, 'Java', 'Ja', 28)"; stmt.executeUpdate(sql); System.out.println("Inserted records into the table..."); }catch (SQLException se){ se.printStackTrace (); }catch (Exception e){ e.printStackTrace (); }finally { try { if (stmt!=null) conn.close (); }catch (SQLException se){ } try { if (conn!=null) conn.close (); }catch (SQLException se){ se.printStackTrace (); } } System.out.println ("Goodbye!"); } }
四、选择数据库
package com.jdbc; import java.sql.*; //选择数据库 public class SelectDatabase { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/jdbc_db"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main (String[] args){ Connection conn = null; try{ Class.forName ("com.mysql.jdbc.Driver"); System.out.println ("Connecting to a selected database..."); conn = DriverManager.getConnection (DB_URL,USER,PASS); System.out.println ("Connected database successfully..."); }catch (SQLException se){ se.printStackTrace (); }catch (Exception e){ e.printStackTrace (); }finally { try{ if (conn!=null) conn.close (); }catch (SQLException se){ se.printStackTrace (); } } System.out.println ("Goodbye!"); } }
五、查询数据
package com.jdbc; import java.sql.*; //查询数据 public class SelectRecords { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/jdbc_db"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ Class.forName("com.mysql.jdbc.Driver"); System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "SELECT id, first, last, age FROM student"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } rs.close(); }catch(SQLException se){ se.printStackTrace(); }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt!=null) conn.close(); }catch(SQLException se){ } try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } System.out.println("Goodbye!"); } }
六、更新数据
package com.jdbc; import java.sql.*; //更新数据 public class UpdateRecords { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/jdbc_db"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main(String[] args){ Connection conn = null; Statement stmt = null; try { Class.forName ("com.mysql.jdbc.Driver"); System.out.println ("Connecting to a selected datavase..."); conn = DriverManager.getConnection (DB_URL,USER,PASS); System.out.println ("Connected database successfully..."); System.out.println ("Creating statment"); stmt = conn.createStatement (); stmt = conn.createStatement(); String sql = "UPDATE student " + "SET age = 22 WHERE id in (100, 101)"; stmt.executeUpdate(sql); sql = "SELECT id, first, last, age FROM student"; ResultSet rs = stmt.executeQuery(sql); while (rs.next ()){ int id = rs.getInt ("id"); int age = rs.getInt ("age"); String first = rs.getString ("first"); String last = rs.getString ("last"); System.out.print ("ID:"+id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } rs.close (); }catch (SQLException se){ se.printStackTrace (); }catch (Exception e){ e.printStackTrace (); }finally { try { if (stmt!=null) conn.close (); }catch (SQLException se){ se.printStackTrace (); } } System.out.println ("Goodbye!"); } }
七、删除数据
package com.jdbc; import java.sql.*; //删除数据 public class DeleteRecords { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/jdbc_db"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main(String[] args){ Connection conn = null; Statement stmt = null; try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "DELETE FROM student " + "WHERE id = 101"; stmt.executeUpdate(sql); sql = "SELECT id, first, last, age FROM student"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } rs.close (); }catch (SQLException se){ se.printStackTrace (); }catch (Exception e){ e.printStackTrace (); }finally { try { if (stmt!=null) conn.close (); }catch (SQLException se){ se.printStackTrace (); } } System.out.println ("Goodbye!"); } }
八、删除数据库
package com.jdbc; import java.sql.*; //删除数据库 public class DropDatabase { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/"; static final String USER = "root"; static final String PASS = "zhz0514"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ Class.forName("com.mysql.jdbc.Driver"); System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("Connected database successfully..."); System.out.println("Deleting database..."); stmt = conn.createStatement(); String sql = "DROP DATABASE jdbc_db"; stmt.executeUpdate(sql); System.out.println("Database deleted successfully..."); }catch(SQLException se){ se.printStackTrace(); }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt!=null) conn.close(); }catch(SQLException se){ } try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } System.out.println("Goodbye!"); } }