Java操作MySQL

　　file->project structure->Modules->Dependencies->+->JARs or dir...->选择包->apply

　　这里的选择包：mysql-connector-java-5.1.44-bin.jar，导入之后重启idea

2.用IDEA来执行sql语句

2.1 通过反射加载

Class.forName("com.mysql.jdbc.Driver");

2.2 与MySQL建立连接

Connection connection= DriverManager.getConnection("jdbc:mysql://master:3306/db01","root", "123456");

2.3创建执行器

创建执行器有两种方式：

createStatement()

prepareStatement（）

Statement statement = connection.createStatement();

2.4执行sql语句

String sql ="select * from student";
ResultSet rs = statement.executeQuery(sql);

2.4显示结果

 while (rs.next()){
            System.out.println(rs.getString(2));
        }

2.5 关闭连接（从下往上）

rs.close();
statement.close();
connection.close();

3. 完整代码

3.1 MySQL查询功能

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.ResultSet;
 4 import java.sql.Statement;
 5 
 6 public class MysqlSelect {
 7     public static void main(String[] args) throws Exception {
 8         //jdbc的使用
 9         //1.通过反射加载驱动
10         Class.forName("com.mysql.jdbc.Driver");
11         //2.建立链接
12         Connection connection= DriverManager.getConnection("jdbc:mysql://master:3306/db01",
13                 "root", "123456");
14         //3.创建执行器
15         //3.1  createStatement()
16         //3.2  prepareStatement(参数)
17         Statement statement = connection.createStatement();
18         //4.执行sql语句
19         String sql =" select * from user";
20         ResultSet rs = statement.executeQuery(sql);
21         //5.获取结果
22         while(rs.next()){
23             System.out.println(rs.getString("user"));
24             System.out.println(rs.getString("password"));
25         }
26         //6.关闭
27         rs.close();
28         statement.close();
29         connection.close();
30     }
31 }

3.2 MySQL删除功能

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.PreparedStatement;
 4 import java.sql.Statement;
 5 
 6 public class MysqlDelete {
 7     public static void main(String[] args) throws Exception {
 8         Class.forName("com.mysql.jdbc.Driver");
 9         Connection connection= DriverManager.getConnection("jdbc:mysql://master:3306/db01",
10                 "root", "123456");
11         String sql="delete from user where user='123'";
12         String sql1="delete from user where password='12345'";
13         Statement statement = connection.createStatement();
14         int i1 = statement.executeUpdate(sql1);
15         PreparedStatement ps = connection.prepareStatement(sql);
16         int i = ps.executeUpdate();
17         System.out.println(i);
18         System.out.println(i1);
19         ps.close();
20         statement.close();
21         connection.close();
22     }
23 }

3.3 MySQL改表功能

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class MysqlUpdata {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/db01",
                "root","123456");
        String sql="update user set password='123' where user='master'";
        PreparedStatement ps = connection.prepareStatement(sql);
        int i = ps.executeUpdate();
        System.out.println(i);
        ps.close();
        connection.close();
    }
}

3.4 MySQL增加功能

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.Statement;
 4 import java.util.Scanner;
 5 
 6 public class MysqlInsert {
 7     public static void main(String[] args) throws Exception {
 8         Scanner scanner = new Scanner(System.in);
 9         String user = scanner.next();
10         String password = scanner.next();
11         //1.通过反射加载驱动
12         Class.forName("com.mysql.jdbc.Driver");
13         //2.建立连接
14         Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/db01",
15                 "root","123456");
16         //3.创建执行器
17         Statement statement = connection.createStatement();
18         //4.执行sql语句
19         String sql="insert into user values("+user+","+password+")";
20         //5.获取结果
21         int i = statement.executeUpdate(sql);
22         System.out.println(i);
23         if(i==1){
24             System.out.println("登陆成功");
25         }else {
26             System.out.println("登录失败");
27         }
28         statement.close();
29         connection.close();
30     }
31 }