IDEA使用JDBC连接MYSQL库

在MYSQL学习过程中，我们可以使用IDEA来连接MSYQL库，对MSYQL库进行操作，这里需要用到JDBC
我使用的版本是 mysql-connector-java-5.1.17，可以根据自己的mysql版本进行调整
可以进入MAVEN仓库 https://mvnrepository.com/ 搜索MySQL

选择自己需要的版本

下载完成之后使用IDEA导入jar包，因为IDEA版本不同这个Project Structure可能在IDEA界面右边的小齿轮

打开之后导入你的JAR包，导入后需要点击一下APPLY，否则可能不会显示。

这样便是成功导入，下面来建立连接。

mysqltestDemo

import java.sql.*;

public class mysqltestdemo {
    public static void main(String[] args) throws Exception {
//     JDBC的使用
//        1.通过反射加在 加载驱动
         Class.forName("com.mysql.jdbc.Driver");
//        2.建立连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia", 
                                                            "root", "123456");
//        3.创建执行器,用来执行SQL语句
//          1.createStatement()
//            2.prepareStatement()
        Statement statement = connection.createStatement();
        String sql ="select * from student";
//        4.执行SQL语句
        ResultSet rs = statement.executeQuery(sql);
//        5.获取结果
        System.out.println(rs);
//        6.关闭
        rs.close();
        statement.close();
        connection.close();
    }

import java.sql.*;

public class mysqltestdemo {
    public static void main(String[] args) throws Exception {
//     JDBC的使用
//        1.通过反射加在 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
//        2.建立连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                "root", "123456");
//        3.创建执行器,用来执行SQL语句
//          1.createStatement()
//            2.prepareStatement()
        Statement statement = connection.createStatement();
        String sql ="select * from student";
//        4.执行SQL语句
        ResultSet rs = statement.executeQuery(sql);
//        5.获取结果
        while(rs.next()){
            System.out.println(rs.getInt("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getInt("sex"));
            System.out.println(rs.getString("age"));
        }
//        6.关闭
        rs.close();
        statement.close();
        connection.close();
    }
}

mysqlInsert

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class mysqlnsert {
    public static void main(String[] args) throws Exception {

        Scanner scanner = new Scanner(System.in);
        String name = scanner.next();
        int age=scanner.nextInt();
        String sex=scanner.next();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                "root", "123456");
        Statement statement = connection.createStatement();

        String sql="insert into student(name,age,sex) values(\""+name+"\","+age+",\""+sex+"\")";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        if(i==1){
            System.out.println("注册成功");
        }else{
            System.out.println("注册失败");
        }
        statement.close();
        connection.close();
    }
}

mysqlDelete

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class mysqlDelete {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                "root", "123456");
        Statement statement = connection.createStatement();
        String sql="delete from student where name='zhontg'";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        statement.close();
        connection.close();
    }
}

msyqlUpdate

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class mysqlDelete {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                "root", "123456");
        Statement statement = connection.createStatement();
        String sql="delete from student where name='zhontg'";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        statement.close();
        connection.close();
    }
}

Login01

package mysql.statement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Login01 {
    public static void main(String[] args) throws  Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户：");
        String username = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                "root", "123456");
        Statement statement = connection.createStatement();
//      1.通过username去mysql中查找有没有这一条记录(存在：输入密码，不存在：报错)
//      2.存在之后，用查到的密码匹配输入的密码
        String sql="select * from user where username='"+username+"'";
        ResultSet rs = statement.executeQuery(sql);
        if(!rs.next()){
            System.out.println("用户输出错误");
//            结束
        }
//        匹配密码
        String password1 = rs.getString("password");
        if(password==null || !password.equals(password1)){
            System.out.println("密码不匹配失败");
        }else {
            System.out.println("登陆成功");
        }
        rs.close();
        statement.close();
        connection.close();
    }
}

注意：这里使用 createStatement 之后，在使用 123' or '1=1时会直接登录成功，这是因为产生了sql注入
sql注入：参数传递时，参数中的内容当做关键字来使用
优化：使用prepareStatement来进行

package mysql.statement;

import java.sql.*;
import java.util.Scanner;

public class Login02 {
    public static void main(String[] args) throws  Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.next();

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                "root", "123456");
        String sql="select * from user where username=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,username);
//      1.通过username去mysql中查找有没有这一条记录(存在：输入密码，不存在：报错)
//      2.存在之后，用查到的密码匹配输入的密码
        ResultSet rs = statement.executeQuery();
        if(!rs.next()){
            System.out.println("用户输出错误");
//            结束
        }
//        匹配密码
        String password1 = rs.getString("password");
        if(password==null || !password.equals(password1)){
            System.out.println("密码不匹配失败");
        }else {
            System.out.println("登陆成功");
        }
        rs.close();
        statement.close();
        connection.close();
    }
}

MysqlUtil

package mysql.uitl;

import javax.xml.transform.Result;
import java.sql.*;

public class mysqlUtil {
   static Connection connection=null;
   static PreparedStatement ps = null;
   static ResultSet rs=null;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConn(){
        try {
            connection = DriverManager.getConnection("jdbc:mysql://master:3306/shujia",
                     "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
//    执行器
    public static PreparedStatement getPs(String sql){
        try {
            ps = connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ps;
    }
    public static ResultSet getRs() {
        try {
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    //增删改
    public static int getInsert() {
        int i = 0;
        try {
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    public static void close(){
        //conn ps rs
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

package mysql.Base;
import mysql.uitl.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcUtiltest {
    public static void main(String[] args) {
        mysqlUtil.getConn();
        String sql="insert into user(username,password) values('test','test')";
        PreparedStatement ps = mysqlUtil.getPs(sql);
//        try {
//            ps.setString(1,"test");
//            ps.setString(2,"test");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        int insert = mysqlUtil.getInsert();
        System.out.println(insert);
    }
}

使用配置文件进行操作

配置文件内容

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://master:3306/shujia
username=root
password=123456

mysqluntil文件

package mysql.uitl;

import mysql.res;

import javax.xml.transform.Result;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class mysqlUtil {
    static String DRIVER;
    static String URL;
    static String USERNAME;
    static String PASSWORD;
    static Connection connection=null;
    static PreparedStatement ps = null;
    static ResultSet rs=null;
    static {
        try {
            //用来操作配置文件当中的信息
            Properties properties = new Properties();
            //反射去加载配置文件
            InputStream is = res.class.getClassLoader().getResourceAsStream("mysql.properties");
            //使用properties对象加载输入流
            properties.load(is);
            DRIVER = properties.getProperty("driver");
            URL = properties.getProperty("url");
            USERNAME = properties.getProperty("username");
            PASSWORD = properties.getProperty("password");
            Class.forName(DRIVER);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConn(){
        try {
            connection = DriverManager.getConnection(URL,
                    USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //    执行器
    public static PreparedStatement getPs(String sql){
        try {
            ps = connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ps;
    }
    public static ResultSet getRs() {
        try {
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    //增删改
    public static int getInsert() {
        int i = 0;
        try {
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    public static void close(){
        //conn ps rs
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

package test;
import mysql.bean.User;
import mysql.uitl.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class LoginTest {
    public static void main(String[] args)throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.next();

        //点击登录->调用后台方法
        User user = login(username, password);
        if(user==null){
            System.out.println("用户错误");
        }else {
            if(password.equals(user.getPassword())){
                System.out.println("登录成功");
            }
        }
    }
    public static User login(String username,String password) throws  Exception{
        User user=null;
        Connection conn = mysqlUtil.getConn();
        String sql="select * from user where username=?";
        PreparedStatement ps = mysqlUtil.getPs(sql);
        ps.setString(1,username);
        ResultSet rs = mysqlUtil.getRs();//是保存到一个对象中
        //验证当前用户是否存在
        if(!rs.next()){
            return user;
        }
        int id = rs.getInt("id");
        String password1 = rs.getString("password");
        user=new User(id,username,password1);
        return user;
    }
}

posted @ 2021-09-07 23:36 钟心意阅读(561) 评论(0) 收藏举报

刷新页面返回顶部

钟心意

IDEA使用JDBC连接MYSQL库

公告