[大数据]JAVA 常用方法实例 JDBC驱动连接MySQL增删改查

模板语句

DriverManager

加载驱动默认方法语句 通常不这么写，知道就可以

DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 

加载驱动固定写法

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

获取数据库驱动对象

Connection connection = DriverManager.getConnection(url,username,password);

connection代表数据库
设置数据库自动提交
事物提交
事物回滚

URL

jdbc:mysql://主机IP:端口/数据库名?

String url = "jdbc:mysql://82.157.178.132:3306/mydb?userUnicode=true&characterEncoding=utf-8&useSSL=true";

Statement

执行类：执行SQL的对象

statement.executeUpdate();	//更新、插入、删除
statement.executeQuery();	//查询操作返回 ResultSet
statement.executeBatch();	//执行多个SQL
statement.execute();		//执行任何SQL语句（效率低点）

ResultSet

查询结果集，封装了所有的查询结果

resultSet.getObject();	//在不知道类型的情况下使用
resultSet.getInt();   	//获得返回值类型为整数的结果集
resultSet.getFloat(); 	//获得返回值类型为浮点数的结果集
resultSet.getString();	//获得返回值类型为字符串的结果集
resultSet.getDate();  	//获得返回值类型为日期的结果集

resultSet.beforeFirst();	//移动到最前
resultSet.afterLast();		//移动到最后
resultSet.next();			//移动到下一行
resultSet.previous();		//移动到前一行
resultSet.absolute();		//移动到指定行

开始编

配置文件db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://82.157.178.132:3306/mydb?userUnicode=true&characterEncoding=utf-8&useSSL=true
username=weijun901
password=123456

工具类JdbcUtils.java

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

public class JdbcUtils {
  private static String driver = null;
  private static String url = null;
  private static String username = null;
  private static String password = null;

  //读取配置文件
  static {
    try {
      InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("./MySQL/db.properties");
      Properties properties = new Properties();
      properties.load(in);

      properties.getProperty("driver");

      driver = properties.getProperty("driver");
      url = properties.getProperty("url");
      username = properties.getProperty("username");
      password = properties.getProperty("password");

      //1.驱动只用加载一次
      Class.forName(driver);

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  //获取连接
  public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, username, password);
  }

  //释放资源
  public static void release(Connection conn, Statement st, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (st != null) {
      try {
        st.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

不安全的Statement (MySQL注入)

增Insert.java

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

public class Insert {
  public static void main(String[] args) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      //获取数据库连接
      conn = JdbcUtils.getConnection();
      //获取Statement对象
      st = conn.createStatement();
      String sql = "insert into user(id,`username`,`password`)\n" + "values (7,'weijun901','123456')";
      //执行SQL语句
      int i = st.executeUpdate(sql);
      if (i > 0) {
        System.out.println("插入成功");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      //释放资源
      JdbcUtils.release(conn, st, null);
    }

  }
}

删Delete.java

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

public class Delete {
  public static void main(String[] args) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      //获取数据库连接
      conn = JdbcUtils.getConnection();
      //获取Statement对象
      st = conn.createStatement();
      String sql = "delete from user where id = 7";
      //执行SQL语句
      int i = st.executeUpdate(sql);
      if (i > 0) {
        System.out.println("删除成功");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      //释放资源
      JdbcUtils.release(conn, st, null);
    }

  }
}

改Update.java

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

public class Update {
  public static void main(String[] args) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      //获取数据库连接
      conn = JdbcUtils.getConnection();
      //获取Statement对象
      st = conn.createStatement();
      String sql = "update user set `userName` = 'weijun',`password`='654321' where id=7";
      //执行SQL语句
      int i = st.executeUpdate(sql);
      if (i > 0) {
        System.out.println("修改成功");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      //释放资源
      JdbcUtils.release(conn, st, null);
    }

  }
}

查Query.java

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

public class Query {
  public static void main(String[] args) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      //获取数据库连接
      conn = JdbcUtils.getConnection();
      //获取Statement对象
      st = conn.createStatement();

      //执行SQL语句
      String sql = "select * from user where id=7";

      rs = st.executeQuery(sql);
      if (rs.next()) {
        System.out.println(rs.getString("id"));
        System.out.println(rs.getString("username"));
        System.out.println(rs.getString("password"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      //释放资源
      JdbcUtils.release(conn, st, rs);
    }

  }
}

登录业务Login.java

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

public class Login {
  public static void main(String[] args) {
    //正常登录
    //login("weijun","654321");
    login("'or '1=1", "'or '1=1");
  }

  //登录业务
  public static void login(String username, String password) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      //获取数据库连接
      conn = JdbcUtils.getConnection();
      //获取Statement对象
      st = conn.createStatement();

      //执行SQL语句
      String sql = "select * from user where `username`='" + username + "' AND `password` = '" + password + "'";

      rs = st.executeQuery(sql);
      if (rs.next()) {
        System.out.println(rs.getString("id"));
        System.out.println(rs.getString("username"));
        System.out.println(rs.getString("password"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      //释放资源
      JdbcUtils.release(conn, st, rs);
    }
  }

}

安全的PreparedStatement

数据库增加了date字段

增InsertMySQL.java

import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class InsertMySQL {
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;

    try {
      conn = JdbcUtils.getConnection();

      //区别
      //使用？占位符代替参数
      String sql = "insert into user(`id`,`username`,`password`,`date`) values(?,?,?,?)";

      st = conn.prepareStatement(sql);   //预编译SQL，先写SQL，不执行

      //手动给参数赋值
      st.setInt(1, 8);
      st.setString(2, "jinting1027");
      st.setString(3, "654321");
      //注意点：sql.date    数据库    java.sql.Date
      //      util.date   Java      new Date().getTime()  获取时间戳
      st.setDate(4, new java.sql.Date(new Date().getTime()));

      //执行
      int i = st.executeUpdate();
      if (i > 0) {
        System.out.println("插入成功");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtils.release(conn, st, null);
    }

  }
}

删DeleteMySQL.java

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

public class DeleteMySQL {
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;

    try {
      conn = JdbcUtils.getConnection();

      //区别
      //使用？占位符代替参数
      String sql = "delete from user where id=?";

      //预编译SQL，先写SQL，不执行
      st = conn.prepareStatement(sql);

      //手动给参数赋值
      st.setInt(1, 8);

      //执行
      int i = st.executeUpdate();
      if (i > 0) {
        System.out.println("删除成功");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtils.release(conn, st, null);
    }

  }
}

改UpdateMySQL.java

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

public class UpdateMySQL {
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;

    try {
      conn = JdbcUtils.getConnection();

      //区别
      //使用？占位符代替参数
      String sql = "update user set `username`=? where id=?;";

      //预编译SQL，先写SQL，不执行
      st = conn.prepareStatement(sql);

      //手动给参数赋值
      st.setString(1, "jinting1027");
      st.setInt(2, 7);

      //执行
      int i = st.executeUpdate();
      if (i > 0) {
        System.out.println("更新成功");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtils.release(conn, st, null);
    }

  }
}

查QueryMySQL.java

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

public class QueryMySQL {
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
      conn = JdbcUtils.getConnection();

      String sql = "select * from user where id = ?";

      st = conn.prepareStatement(sql);

      st.setInt(1, 7);//传递参数
      rs = st.executeQuery();

      //执行
      if (rs.next()) {
        System.out.println(rs.getString("id"));
        System.out.println(rs.getString("username"));
        System.out.println(rs.getString("password"));
        System.out.println(rs.getString("date"));
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JdbcUtils.release(conn, st, rs);
    }
  }
}

登录业务LoginMySQL.java

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

public class LoginMySQL {
  public static void main(String[] args) {
    //正常登录
    login("weijun","654321");
    //login("'' or 1=1", "123456");
  }

  //登录业务
  public static void login(String username, String password) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;

    try {
      //获取数据库连接
      conn = JdbcUtils.getConnection();

      // PreparedStatement防止SQL注入的本质，是把传递进来的参数当做字符
      // 假设其中存在转义字符，会被直接转义
      String sql = "select * from user where `username`=? and `password`=?";  //Mybatis

      st = conn.prepareStatement(sql);
      st.setString(1,username);
      st.setString(2,password);

      rs = st.executeQuery();
      if (rs.next()) {
        System.out.println(rs.getInt("id"));
        System.out.println(rs.getString("username"));
        System.out.println(rs.getString("password"));
        System.out.println(rs.getDate("date"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      //释放资源
      JdbcUtils.release(conn, st, rs);
    }
  }
}