实验一(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
+-------------------------------------------------------------------------+

                                 删除整个数据库,任务完成！！！