实验一(jdbc连接数据库）

第一步 打开mysql

第二步创建工程并添加导包    教程：https://jingyan.baidu.com/article/3aed632e1a4ceb70108091f6.html

第三步，编写代码：

主操作类：

import java.sql.DriverManager;  import java.sql.ResultSet;  import com.mysql.jdbc.Connection;  import com.mysql.jdbc.Statement;  public class JdbcDemo   {      public static void main(String[] args)  {             ResultSet result = null;          Statement statement = null;          try {              Class.forName("com.mysql.jdbc.Driver");    //加载jdbc驱动              String url = "jdbc:mysql://localhost:3306/mysql?useUnicode"  //地址                            + "=true&characterEncoding=utf-8&useSSL=false";              String username = "root";  //用户名            String password = "tyz19960912";  //密码            Connection connection  = (Connection) DriverManager.getConnection(url,username,password);              statement = (Statement) connection.createStatement();  //连接实例            /*创建*/            JdbcCreate jdb1=new JdbcCreate();            jdb1.op(statement,result);            System.out.println("                                       第一次操作： 数据表创建成功！打印数据");            JdbcSelect jdb5=new JdbcSelect();            jdb5.op(statement,result);            System.out.println(" ");            /*插入数据*/            Jdbcinsert_1 jdb2=new Jdbcinsert_1();            jdb2.op(statement,result);            System.out.println("                                       第二次操作： users,person 插入数据成功！打印数据");            JdbcSelect jdb3=new JdbcSelect();            jdb3.op(statement,result);            System.out.println(" ");            /*插入数据*/            Jdbclnsert jdb31=new Jdbclnsert();            jdb31.op(statement,result);            System.out.println("                                       第三次操作： users,person 插入数据成功！打印数据");            JdbcSelect jdb4=new JdbcSelect();            jdb4.op(statement,result);            System.out.println(" ");            /*插入数据*/            JdbcDrop jdb51=new JdbcDrop();            jdb51.op(statement,result);            System.out.println("                                       第四次操作： 删除数据成功！打印数据");            JdbcSelect jdb41=new JdbcSelect();            jdb41.op(statement,result);            System.out.println(" ");            String query = "drop table users,person";//删除             statement.executeUpdate(query);            System.out.println("                                       删除整个数据库,任务完成！！！");         }     catch (Exception e) {             System.out.println(e.getMessage());           }      }  }  

创建操作

import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Statement;public class JdbcCreate { //创建函数    public void op (Statement statement, ResultSet result) throws SQLException {         String sql = "CREATE TABLE users"                    +  "("                    +  "  username varchar(10) not null,"                    +  "  pass     varchar(8)  not null,"                    +  "  PRIMARY KEY ( username )"                    +  ")";            statement.executeUpdate(sql);                sql = "CREATE TABLE person"                    +  "("                    +  "  username varchar(10) not null,"                    +  "  name     varchar(20) not null,"                    +  "  age      int default 18  ,"                    +  "  teleno   char(11) default '18877009966'  ,"                    +  "  PRIMARY KEY ( name )"                    + ")";            statement.executeUpdate(sql);    }}

第一次插入操作

import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Statement;public class Jdbcinsert_1 {public void op (Statement statement, ResultSet result) throws SQLException {        String []sql= {"insert into users(username,pass) values('ly','123456')",                       "insert into users(username,pass) values('liming','345678')",                       "insert into users(username,pass) values('test','11111')",                       "insert into users(username,pass) values('test1','12345')",                       "insert into person(username,name) values('ly','雷力')",                       "insert into person(username,name,age) values('liming','李明',25)",                       "insert into person(username,name,age,teleno) values('test','测试用户',20,'13388449933')"};        for(int x=0;x<7;x++) {            statement.executeUpdate(sql[x]);        }    }}

第二次插入操作：

import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Statement;public class Jdbclnsert {     public void op (Statement statement, ResultSet result) throws SQLException {         String []s0 = {"ly", "test2",  "test1", "test", "admin" }; //username         String []s1 = {"88888"};         String []s2 = {"王五","测试用户2","测试用户1","张三","admin"}; //name         String []s3 = {"0", "2", "33","23", "0"}; //age         String []s4 = {"NULL", "NULL",  "NULL", "18877009966","NULL"}; //TELENO         for(int i=0;i<5;i++){              String sql;              String query = "SELECT * FROM person where username='"+s0[i]+"'";               result = statement.executeQuery(query);               String temp =s0[i];              String temp1=s1[0];              String temp2=s2[i];              String temp3=s3[i];              String temp4=s4[i];              if(!result.next()) {                  if(i!=2) {                  sql="insert into users(username,pass) values('"+temp+"','"+temp1+"')";                  statement.executeUpdate(sql);                  }                  sql="insert into person(username,name) values('"+temp+"','"+temp2+"')";                  statement.executeUpdate(sql);              }              else {                  sql="update person set name='"+temp2+"' where username='\"+temp+\"'";                  sql="update person set pass='\"+temp1+\"' where username='\"+temp+\"'";                  sql="update person set age='"+temp3+"' where username='\"+temp+\"'";                  sql="update person set teleno='"+temp4+"' where username='\"+temp+\"'";                  statement.executeUpdate(sql);              }         }    }}

删除操作

import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Statement;public class JdbcDrop {  //删除函数    public void op (Statement statement, ResultSet result) throws SQLException {         String query = "delete from users where username like 'test%'";//删除          statement.executeUpdate(query);         query = "delete from person where username like 'test%'";//删除          statement.executeUpdate(query);        }}

查询函数

import java.sql.ResultSet;import java.sql.SQLException;import com.mysql.jdbc.Statement;public class JdbcSelect {  //查询函数    public void op (Statement statement, ResultSet result) throws SQLException{     String query = "SELECT * FROM users";      System.out.println("表users:");     System.out.println("+*************************************************************************+");     System.out.println("|字段名 username                   字段名 pass                    |");     System.out.println("+-------------------------------------------------------------------------+");     result = statement.executeQuery(query);            while(result.next())  {               String s1 = result.getString(1);               String s2 = result.getString(2);               while(s1.length()<20) {                 s1+=' ';             }             System.out.println("|      "+s1+"        "+s2+"");               System.out.println("+-------------------------------------------------------------------------+");         }           //  System.out.println("+-------------------------------------------------------------------------+");         query = "SELECT * FROM person";          System.out.println("表person:");         System.out.println("+*************************************************************************+");         System.out.println("|字段名 username         字段名 name         字段名age          字段名 teleno|");         System.out.println("+-------------------------------------------------------------------------+");         result = statement.executeQuery(query);                while(result.next())  {                   String s1 = result.getString(1);                   String s2 = result.getString(2);                   String s3 = result.getString(3) ;                 String s4 = result.getString(4);                   while(s1.length()<16) s1+=' ';                 while(s2.length()<20) s2+=' ';                 while(s3.length()<16) s3+=' ';                 System.out.println("|      "+s1+" "+s2+" "+s3+" "+s4);                   System.out.println("+-------------------------------------------------------------------------+");             }              //   System.out.println("+-------------------------------------------------------------------------+");    }}

运行效果：

                        第一次操作： 数据表创建成功！打印数据表users:+*************************************************************************+|字段名 username                   字段名 pass                              |+-------------------------------------------------------------------------+表person:+*************************************************************************+|字段名 username         字段名 name         字段名age       字段名 teleno   |+-------------------------------------------------------------------------+

                        第二次操作： users,person 插入数据成功！打印数据表users:+*************************************************************************+|字段名 username                   字段名 pass                              |+-------------------------------------------------------------------------+|      liming                      345678+-------------------------------------------------------------------------+|      ly                          123456+-------------------------------------------------------------------------+|      test                        11111+-------------------------------------------------------------------------+|      test1                       12345+-------------------------------------------------------------------------+表person:+*************************************************************************+|字段名 username        字段名 name         字段名age       字段名 teleno    |+-------------------------------------------------------------------------+|      liming           李明               25             18877009966+-------------------------------------------------------------------------+|      test             测试用户            20             13388449933+-------------------------------------------------------------------------+|      ly               雷力               18              18877009966+-------------------------------------------------------------------------+

                        第三次操作： users,person 插入数据成功！打印数据表users:+*************************************************************************+|字段名 username                   字段名 pass                             |+-------------------------------------------------------------------------+|      admin                       88888+-------------------------------------------------------------------------+|      liming                      345678+-------------------------------------------------------------------------+|      ly                          123456+-------------------------------------------------------------------------+|      test                        11111+-------------------------------------------------------------------------+|      test1                       12345+-------------------------------------------------------------------------+|      test2                       88888+-------------------------------------------------------------------------+表person:+*************************************************************************+|字段名 username        字段名 name         字段名age        字段名 teleno   |+-------------------------------------------------------------------------+|      admin            admin             18               18877009966+-------------------------------------------------------------------------+|      liming           李明               25               18877009966+-------------------------------------------------------------------------+|      test             测试用户            20               13388449933+-------------------------------------------------------------------------+|      test1            测试用户1           18               18877009966+-------------------------------------------------------------------------+|      test2            测试用户2           18               18877009966+-------------------------------------------------------------------------+|      ly               雷力                18               18877009966+-------------------------------------------------------------------------+

                        第四次操作： 删除数据成功！打印数据表users:+*************************************************************************+|字段名 username                   字段名 pass                             |+-------------------------------------------------------------------------+|      admin                       88888+-------------------------------------------------------------------------+|      liming                      345678+-------------------------------------------------------------------------+|      ly                          123456+-------------------------------------------------------------------------+表person:+*************************************************************************+|字段名 username        字段名 name         字段名age       字段名 teleno |+-------------------------------------------------------------------------+|      admin            admin              18               18877009966+-------------------------------------------------------------------------+|      liming           李明                25               18877009966+-------------------------------------------------------------------------+|      ly               雷力                18               18877009966+-------------------------------------------------------------------------+                                 删除整个数据库,任务完成！！！