java操作数据库(JDBC)

JDBC:

executeUpdate()

 

int excuteUpdate(String sql) 执行数据库中的SQL语句, insert delete update

返回值int,表示操作成功数据表多少行

 

 

package jdbc;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

 

 

/*

 * JDBC操作数据库的步骤

 *   1.注册驱动   告诉JVM使用的是哪一个的数据库的驱动

 *   2.获得连接    使用JDBC中的类,完成对my sql 数据库的连接

 *   3.获得语句执行平台    通过连接对象获取SQL语句的执行者对象

 *   4.执行SQL语句   使用执行者对象向数据库执行SQL语句,获取到数据库执行的结果

 *   5.处理结果   

 *   6.释放资源

 */

public class JDBCdemo {

       public static void main(String[] args) throws  ClassNotFoundException, SQLException {

              //1.注册驱动        反射技术将驱动类加人到内容

              //使用java.sql.DriverManager类的静态方法registerDriver(Driver driver)

              //Driver是一个接口,参数传递,MySql驱动程序中的实现类

              //DriverManager.registerDriver(new Driver());源码中已存在,不推荐重复写

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

             

              //2.获得数据库连接    DriverManager类中的静态方法

              //static Connection getConnection(String url,String user,String password)

              //返回值是Connection接口的实现类,在mysql驱动程序

              //url:数据库地址  jdbc:mysql://连接主机IP:端口号/数据库名字

              //url:数据库地址  jdbc:mysql://localhost:33060/mybase

              String url = "jdbc:mysql://localhost:33060/mybase";

              String userName = "root";

              String password = "root";

              Connection con = DriverManager.getConnection(url,userName,password);

             

              //3.获得语句执行平台,通过数据库连接对象,获取到SQL语句的执行者对象

              //con对象调用方法 Statement createStatement() 获取Statement对象,将SQL语句发送到数据库

              //返回值是Statement接口中的实现类对象,在mysql驱动程序

              Statement stat = con.createStatement();

             

              //4.执行SQL语句,

              //通过执行者对象调用方法执行SQL语句,获取结果

              //int excuteUpdate(String sql) 执行数据库中的SQL语句, insert delete update

              //返回值int,表示操作成功数据表多少行

              int row = stat.executeUpdate("insert into mybase(midnum,mname,sex) values(3,'张三','男')");

             

              //5.处理结果,显示成功的语句数

              System.out.println(row);

             

              //6.释放资源

              stat.close();

              con.close();

       }

      

}

*****************************************************************

executeQuery()

ResultSet executeQuery(String sql) 执行SQL语句中的select查询

executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用

 

 

package jdbc;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

/*

 * JDBC技术,查询数据表,获取结果集

 */

public class JDBCDemo1 {

       public static void main(String[] args) throws ClassNotFoundException, SQLException {

              //1.注册驱动

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

             

              //2.获取连接对象

              String url = "jdbc:mysql://localhost:33060/mybase";

              String user = "root";

              String password = "root";

              Connection con = DriverManager.getConnection(url, user, password);

             

              //3.获取执行SQL语句对象

              Statement stat = con.createStatement();

             

              //4.调用执行者对象方法,执行SQL语句获得结果集

              //ResultSet executeQuery(String sql) 执行SQL语句中的select查询

//executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我//们使用

              //返回ResultSet接口的实现类对象,实现类在mysql驱动中

              String sql = "select * from mybase";

              ResultSet re =  stat.executeQuery(sql);

             

              //5.处理结果集

              //ResultSet接口方法 boolean next() 返回true有结果集,返回false则没有

              while(re.next()) {

                     System.out.println(re.getInt("midnum")+"  "+

                                          re.getString("mname")+"  "+re.getString("sex"));

              }

             

              //6.关闭资源

              re.close();

              stat.close();

              con.close();

       }

}

******************************************************************************

注入攻击

package jdbc;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

 

/*

 * java程序实现用户名登录,用户名和密码,数据库检查

 * 演示被别人注入攻击

 */

public class JDBCDemo2 {

       public static void main(String[] args) throws ClassNotFoundException, SQLException {

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

              //连接数据库

              String url = "jdbc:mysql://localhost:33060/mybase";

              String username = "root";

              String password = "root";

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

             

              Statement stat = con.createStatement();

             

              Scanner sc = new Scanner(System.in);

              String user  = sc.nextLine();

              String pass = sc.nextLine();

             

              /*

               * SELECT * FROM users WHERE username='张三' AND PASSWORD='123' OR 1=1;

               * 注入攻击,此时永远返回true

               * 输入的pass为       任意值 'or '1=1               时永远为真

               */

              String sql = "SELECT * FROM users WHERE username='"+user

                            +"' AND PASSWORD='"+pass+"'";

             

              ResultSet re = stat.executeQuery(sql);

              while(re.next()) {

                     System.out.println(re.getString("username") + re.getString("password"));

              }

             

              re.close();

              stat.close();

              con.close();

       }

}

*************************************************

 

 

 

prepareStatement

,PreparedStatement(SQL预编译存储,多次高效的执行SQL)

PreparedStatement pst = con.prepareStatement(sql);

 

 

package jdbc;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

 

/*

 * java程序实现用户名登录,用户名和密码,数据库检查

 * 防止注入攻击

 * Statement接口有一个子接口,PreparedStatement(SQL预编译存储,多次高效的执行SQL)

 */

public class JDBCDemo3 {

       public static void main(String[] args) throws ClassNotFoundException, SQLException {

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

              //连接数据库

              String url = "jdbc:mysql://localhost:33060/mybase";

              String username = "root";

              String password = "root";

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

             

              Scanner sc = new Scanner(System.in);

              String user  = sc.nextLine();

              String pass = sc.nextLine();

              sc.close();

              //调用SQL语句,数据表,查询用户名和密码,存在则登陆成功,否则登陆失败

              String sql = "SELECT * FROM users WHERE username=? AND PASSWORD=?";

              //调用Connection中的方法preparedStatement,获取preparedStatement接口中的实现类

              //方法中SQL语句的参数全部使用?占位符

              PreparedStatement pst = con.prepareStatement(sql);

              pst.setObject(1, user);

              pst.setObject(2, pass);

              ResultSet rs = pst.executeQuery();

              while(rs.next()) {

                     System.out.println(rs.getString("username") + rs.getString("password"));

              }

             

              rs.close();

              pst.close();

              con.close();

       }

}

数据批处理

package jdbcPool.batch;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

 

import jdbcPool.JDBCUtils;

 

/**

 * 数据库批处理

 */

public class BatchUpdate  {

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

              Connection con = JDBCUtils.getConnection();

              String sql = "insert into test (name,age) values(?,?)";

              PreparedStatement ps = con.prepareStatement(sql);

             

              long begin = System.currentTimeMillis();

              for (int i = 0; i < 1000; i++) {

                     ps.setString(1, "zhangsan");

                     ps.setInt(2, 22);

                     ps.addBatch();

              }

              ps.executeBatch();

              long end = System.currentTimeMillis();

              System.out.println(end-begin);

              System.out.println("success");

       }

}

数据库批处理要求

在连接url后加上: ?rewriteBatchedStatements=true