时间戳

首页 > 技术文章 > JDBC 数据库和IDEA的连接

JDBC 数据库和IDEA的连接

lycc0210 2021-11-08 21:56 原文

一、MySQL中shell编程实现自动查询

编写脚本：mysql.shell

 将shell脚本写入计时器

 执行即可，完成后记得修改计时器文件关闭计时器自动查询数据库

 

 

二、Java操作数据库------JDBC

1、打开IDEA2018.1.5，创建新项目

 

 选择之前安装的JDK1.8版本

 

 下一步

 

 注意这里盘符不要出现\\，如果有删除一个\

 

 任意浏览器搜索maven仓库

 

进入官网完成认证，搜索mysql，选择mysql 连接器，下载里面任意版本都可以

 

 

java操作mysql，导入第三方工具包

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

 

 测试连接

1、使用createStatement执行器

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

/*
1、加载驱动（引用项目中的代码）通过反射
2、获取连接（指定ip地址，用户名，密码）
        String url="jdbc:mysql://master:3306/lycc";
                 连接的数据库    IP地址，数据库端口  数据库名称
        String username="root";  虚拟机用户名称
        String password="123456";  用户密码
         Connection connection = DriverManager.getConnection(url, username, password);
3.获取执行器 createStatement(会出现sql注入不使用)和prepareStatement
        PreparedStatement ps = connection.prepareStatement(sql);//给sql的格式(模板)
        ps.setString(1,"1012");
        ps.setString(2,"test");
4.获取结果(sql语句为增删改查操作,不需要解析结果,使用executeUpdate())
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            String name = rs.getString("name");
            System.out.println(name);
        }
5.关闭连接(从下向上关闭)
        rs.close();
        ps.close();
        conn.close();
 */
public class Mysqltext {
    public static void main(String[] args) throws  Exception{
        //加载第三方工具
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");

        //执行mysql
        //获取MySQL
        String sql = "select * from stu" ;
        //获取执行器
        Statement statement = conn.createStatement();
        //执行sql语句，分为增删改（返回boolean类型，要么成功1，要么失败0）和查（返回多个结果）
        ResultSet resultSet = statement.executeQuery(sql);

        //获取结果
        while(resultSet.next()){
            String string = resultSet.getString(1);//数据库表下标索引从1开始
            System.out.println(string);
        }

        resultSet.close();
        statement.close();
        conn.close();
    }
}

 

 修改数据（增加，删除，修改）

增加

package com.lycc.mysql;

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

public class MysqlInsert {
    public static void main(String[] args) throws Exception {
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");

        //执行sql
        String name = "'翔阳'";
        String sql1 = "insert into stu(id,name,age,sex) values(1016,"+name+",18,\"1\")";
        String sql2 = "insert into stu(id,name,age,sex) values(1017,'影山',18,\"1\")";
        String sql3 = "insert into stu(id,name,age,sex) values(1018,'研磨',18,'1')";
        System.out.println(sql1);
        System.out.println(sql2);
        System.out.println(sql3);

        //获取执行器
        Statement statement = conn.createStatement();
        //插入数据
        System.out.println(statement.executeUpdate(sql1));
        System.out.println(statement.executeUpdate(sql2));
        System.out.println(statement.executeUpdate(sql3));

        statement.close();
        conn.close();

    }
}

 

 删除

package com.lycc.mysql;

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.cj.jdbc.Driver");

        //获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");

        //删除语句
        String sql ="delete from stu where id=1014"; 
        Statement statement = conn.createStatement();
        System.out.println(statement.executeUpdate(sql));

        statement.close();
        conn.close();
    }
}

 

 

 修改：

package com.lycc.mysql;

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

public class MysqlUpdate {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");

        String sql = "update stu set name='sky' where id=1012";
        System.out.println(sql);
        Statement statement = conn.createStatement();
        System.out.println(statement.executeUpdate(sql));
        statement.close();
        conn.close();
    }
}

 

 

 注意事项：

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

public class Mysqlroot {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");

        String username = "root123' or '1'='1";
        System.out.println(username);

        String sql="select * from user where username='"+username+"'";
        System.out.println(sql);

//        Statement statement = conn.createStatement();
//        ResultSet resultSet = statement.executeQuery(sql);


    }
}

 

模拟登录验证

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

public class LoginText {
    public static void main(String[] args) throws Exception {
        //加载驱动
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");
        //sql语句
//        String username = "root";
//        String password = "123456";
//        String sql = "select * from user where name ='"+ username + "'and password ='"+password+"'";

        String username = "root123' or '1=1";
        String sql = "select * from user where name ='"+ username + "'";
        //获取执行器
        Statement statement = conn.createStatement();
        //执行sql语句
        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("error");
        }
    }
}

 

 

 

 这里出现一个问题，数据库没有root123这个用户却登录成功，这是因为createstatement执行器执行sql语句，会把参数当做sql语句处理

 出现了or判断，无论对错后面的都是正确的

 

 

 

2、使用prepareStatement执行器

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

public class PrepareText {
    public static void main(String[] args) throws Exception {
        //获取驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");

        //定义一个sql语句模板
        String sql ="select * from user where name=?";
        String username ="root123' or '1=1";
        //先把sql模板写入执行器
        PreparedStatement ps = conn.prepareStatement(sql);
        //开始传递参数，下标从1开始
        ps.setString(1,username);
        
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()){
            System.out.println("成功");
        }else{
            System.out.println("失败");
        }
    }
}

 

 

 

 

两种执行器的区别

createStatement

　　先把参数传入sql语句中，把总的当作一个sql语句传入执行器中

prepareStatement

　　先给一个sql语句，然后在传入参数，有前后顺序，能分辨出谁是参数谁是sql语句

 

 练习，验证用户名和用户密码模拟登录

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

public class Login2 {
    //将conn变量提出来变成全局变量
    static  Connection conn =null;
    //静态代码块
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //main方法
    public static void main(String[] args) throws Exception{
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.next();
        System.out.println("请输入密码");
        String password = sc.next();
        System.out.println(text(username, password));
    }

    //正常写法
//    public static String text(String username,String password) throws Exception {
//        String sql = "select * from user where name=?";
//        PreparedStatement ps = conn.prepareStatement(sql);
//        ps.setString(1,username);
//
//        ResultSet resultSet = ps.executeQuery();
//        if (resultSet.next()){
//            System.out.println("用户名正确");
//            //向下继续验证密码,密码相同则成功，否则失败
//            String sqlpassword = resultSet.getString("password");
//            String result = Password(sqlpassword, password);
//            return result;
//        }else{
//            return "用户不存在";
//        }
//    }
//
//    public static String Password(String sqlpassword, String password){
//        if (password.equals(sqlpassword)){
//            return "密码正确，登录成功";
//        }else{
//            return "密码错误,登录失败";
//        }
//    }

    //取反写法，定义用户名验证方法
    public static String text(String username,String password) throws Exception{
        //定义sql语句模板
        String sql = "select * from user where name=?";
        //获取执行器
        PreparedStatement ps = conn.prepareStatement(sql);
        //传参
        ps.setString(1,username);
        //执行
        ResultSet resultSet = ps.executeQuery();
        //判断用户存不存在
        if (!resultSet.next()){
            return "用户名不存在,登录失败";
        }else{
            System.out.println("用户名正确");
            //获取用户名对应的密码
            String sqlPassword = resultSet.getString("password");
            //调用密码比较方法
            String result = Password(sqlPassword, password);
            return result;
        }
    }

    //定义密码比较方法
    public static String Password(String sqlpassword, String password){
        if (!password.equals(sqlpassword)){
            return "密码错误，登陆失败";
        }else{
            return "密码正确，登陆成功";
        }
    }
}

 

 

 

 用户管理系统实现用户修改密码和删除用户

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

/*
用户管理系统
修改
注销

 */
public class UserLogin {
    public static void main(String[] args) throws Exception{
        System.out.println(update("12345645", "root"));
        System.out.println(delete("text"));
    }

    public static String update(String password,String username) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");
        String sql = "update user set password=? where name=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,password);
        ps.setString(2,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "修改失败";
        }else {
            return "修改成功";
        }

    }
    public static String delete(String username) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");
        String sql = "delete from user where name=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "删除失败";
        }else {
            return "删除成功";
        }
    }

}

 

 

 发现我们这里的代码有很多冗余，对其进行优化，部分变量可以提取成全局变量，使用静态代码块只执行一次

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

/*
用户管理系统
修改
注销

 */
public class UserLogin {
    private static  Connection conn =null;
    private static  PreparedStatement ps =null;
    private static ResultSet rs =null;
    static{
       try{
           Class.forName("com.mysql.cj.jdbc.Driver");
           Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/lycc", "root", "123456");
       }catch (Exception e){
           e.printStackTrace();
       }
    }
    public static void main(String[] args) throws Exception{
        System.out.println(update("12345645", "root"));
        System.out.println(delete("text"));
    }
    //修改功能
    public static String update(String password,String username) throws Exception{
        String sql = "update user set password=? where name=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,password);
        ps.setString(2,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "修改失败";
        }else {
            return "修改成功";
        }

    }
    //删除功能
    public static String delete(String username) throws Exception{
        String sql = "delete from user where name=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "删除失败";
        }else {
            return "删除成功";
        }
    }

    //关闭连接
    //增删改查：ps rs conn
    public static void closeAll() throws Exception{
        if (rs!=null){
            rs.close();
        }
        if (ps!=null){
            ps.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

 

地址等参数提取成全局变量

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

/*
用户管理系统
修改
注销

 */
public class UserLogin {
    //业内规则 大写变量一般不要改
    private static String URL="jdbc:mysql://master:3306/lycc";
    private static String DRIVER="com.mysql.cj.jdbc.Driver";
    private static String USERNAME="root";
    private static String PASSWORD="123456";
    private static  Connection conn =null;
    private static  PreparedStatement ps =null;
    private static ResultSet rs =null;

    static{
       try{
           Class.forName(DRIVER);
           conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
       }catch (Exception e){
           e.printStackTrace();
       }
    }
    public static void main(String[] args) throws Exception{
        System.out.println(update("12345645", "root"));
        System.out.println(delete("text"));
    }
    //修改功能
    public static String update(String password,String username) throws Exception{
        String sql = "update user set password=? where name=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,password);
        ps.setString(2,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "修改失败";
        }else {
            return "修改成功";
        }

    }
    //删除功能
    public static String delete(String username) throws Exception{
        String sql = "delete from user where name=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "删除失败";
        }else {
            return "删除成功";
        }
    }

    //关闭连接
    //增删改查：ps rs conn
    public static void closeAll() throws Exception{
        if (rs!=null){
            rs.close();
        }
        if (ps!=null){
            ps.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

 

 创建资源目录，进一步优化，参数放入配置文件中

修改好apply即可

新建配置文件，也可以新建file文件命名加上.properties后缀

 

 

 

 

 

 创建完成

 

 

添加电脑相关参数信息

 

 

 

 读取文件获取参数，改变参数修改配置文件即可

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

/*
用户管理系统
修改
注销

 */
public class UserLogin {
    //业内规则 大写变量一般不要改，从文件读取参数
    private static String URL;
    private static String DRIVER;
    private static String USERNAME;
    private static String PASSWORD;
    private static  Connection conn =null;
    private static  PreparedStatement ps =null;
    private static ResultSet rs =null;

    static{
        try{
            Properties properties = new Properties();
            InputStream is = UserLogin.class.getClassLoader().getResourceAsStream("mysql.properties");
            properties.load(is);
            //从配置文件读取参数
            URL = properties.getProperty("url");
            DRIVER = properties.getProperty("driver");
            USERNAME = properties.getProperty("username");
            PASSWORD = properties.getProperty("password");
            Class.forName(DRIVER);
           conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
       }catch (Exception e){
           e.printStackTrace();
       }
    }
    public static void main(String[] args) throws Exception{
        System.out.println(update("12345645", "root"));
        System.out.println(delete("text"));
    }
    //修改功能
    public static String update(String password,String username) throws Exception{
        String sql = "update user set password=? where name=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,password);
        ps.setString(2,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "修改失败";
        }else {
            return "修改成功";
        }

    }
    //删除功能
    public static String delete(String username) throws Exception{
        String sql = "delete from user where name=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        int i = ps.executeUpdate();
        if (i!=1){
            return "删除失败";
        }else {
            return "删除成功";
        }
    }

    //关闭连接
    //增删改查：ps rs conn
    public static void closeAll() throws Exception{
        if (rs!=null){
            rs.close();
        }
        if (ps!=null){
            ps.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

 

 

 引入JDBCUtil，将工具类写入JDBCUtil，其他类直接引用，减少代码冗余

1、配置文件

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

 

2、 JDBCUtil工具类

package com.lycc.mysql.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

//避免代码冗余
//针对多类用户，每一类用户都需要连接mysql
/*
1、加载驱动
2、获取连接
3、执行器（每类用户sql不同，可写可不写）
4、关闭

JDBCUtil工具类写的越具体，引用他其他类写的功能越少
 */
public class JDBCUtil {
    private static String DRIVER;
    private static String URL;
    private static String USERNAME;
    private static String PASSWORD;
    private static Connection conn ;
    private static PreparedStatement ps=null;
    private static  ResultSet rs=null;
    //加载驱动，获取连接
    static{
        try{
            Properties properties = new Properties();
            InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("mysql.properties");
            properties.load(is);
            DRIVER=properties.getProperty("driver");
            URL=properties.getProperty("url");
            USERNAME=properties.getProperty("username");
            PASSWORD=properties.getProperty("password");
            Class.forName(DRIVER);
            conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //获取连接，别的类调用util类可以获取conn
    public static Connection getConn(){
       return conn;
    }

    //获取执行器的方法
    public static PreparedStatement getPs(String sql){
        try {
            ps = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ps;
    }

    //获取结果集
    public static ResultSet getRs(){
        try {
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    //获取update结果
    public static int update(){
        int i=0;
        try {
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //显示结果集合,显示用户名
    public static String showRs()  {
        String name=null;
        try{
            while(rs.next()){
                name = rs.getString(1);
                System.out.println(name);
            }
        }catch(Exception e){
            e.printStackTrace();
        }

        return name;
    }

    //关闭
    public static void closeAll(){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

测试类

import java.io.InputStream;
import java.util.Properties;

public class PropertiesText {
    public static void main(String[] args) throws Exception{
        Properties properties = new Properties();
        //获取配置文件，反射加载获取配置文件
        InputStream is = PropertiesText.class.getClassLoader().getResourceAsStream("mysql.properties");
        properties.load(is);
        String url = properties.getProperty("url");
        System.out.println(url);
    }
}

 

 

 

推荐阅读