Java38: Database Four (Oracle)

The standard for JDBC to provide access to a database is a series of interfaces that define a common approach to accessing a database

The implementation of JDBC is provided by each database vendor

Definition of the JDBC interface

Database vendor-to-JDBC implementation jar

Establish connection-Send sql-execution sql-return result-close connection

JDBC API (interface)

java.sql.connection//Encapsulation and database connection

java.sql.statement//encapsulating the execution of SQL statements

Results of jave.sql.resultset//Package DQL execution

package jdbc;import java.sql.connection;import java.sql.driver;import  java.sql.drivermanager;import java.sql.resultset;import java.sql.sqlexception;import  java.sql.statement;import oracle.jdbc.oracledriver;public class oracledemo01 {public  Static void main (String[] args)  throws exception{//loading JDBC Implementation//driver driver  =new oracledriver ()  ;  write//drivermanager.registerdriver (driver);//load class into memory    static   Fast execution    above two    you don't have to write it yourself//the code in the static block of the class will be driver registered class.forname ("Oracle.jdbc.OracleDriver" )///load JDBC Implementation with this is good//create a connection//Call drivermanager  Getconnection Method//The method returns the object of the database vendor's implementation class to the connection interface ( Because DriverManager has registered the database vendor's driver information) string url =  "Jdbc:oracle:thin: @x1. zongxuan.online:1521:xx"; string user =  "Scott"; string pass =  "xxxxx"; Connection con = drivermanager.getconnection (Url,user,pass);//url  used to represent the databaseConnection information (ip port  database name)    Different database vendors have a specific URL for the format and identity// drivermanager  will select different driver information based on this identity// If multiple database vendors are registered for implementation//user//password//system.out.println (con);//Execute sql//connection  's createstatement ()   The statement  method is used to create an object of the implementation class statement stmt = con.createstatement ();//can only execute DQL statement//return value is resultset// The SQL statement is transferred to the database execution     //Gets the result data of the database transfer     //and encapsulates this data into resultset  Object Resultset rs = stmt.executequery ("Select empno,ename name,sal from emp") ;//Get Results while (Rs.next ()) {//.next ()   cursors   down System.out.println (rs.getstring ("empno") + "," +rs.getstring (" Name ") +", "+rs.getstring (" Sal ")); Rs.close ();//close connection} }

package jdbc;import java.sql.connection;import  java.sql.drivermanager;import java.sql.statement;public class oracledemo02 {public  Static void main (String[] args)  throws exception{class.forname (" Oracle.jdbc.OracleDriver "); string url =  "Jdbc:oracle:thin: @x1. zongxuan.online:1521:xx"; String user= "Scott"; string pass= "xxxxx"; Connection con = drivermanager.getconnection (Url,user,pass); Statement stmt = con.createstatement ();//execute DML statement  insert delete updatestring  sql =  "INSERT INTO EMP2 (Empno,ename,sal,deptno)  values (LMDTX ', 3000,10)"; int  n = stmt.executeupdate (SQL);//Returns an integer representing the number of rows affected by the statement just SYSTEM.OUT.PRINTLN (n); Con.close ();}} 

Package Jdbc;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.statement;public class OracleDemo03 {public static void main (string[] args) throws Exception{class.forname ("Oracle.jdbc.OracleDriver"); String url = "Jdbc:oracle:thin: @x1. zongxuan.online:1521:xx"; String user = "Scott"; String pass = "xxxxx"; Connection con = drivermanager.getconnection (url,user,pass); Statement stmt = Con.createstatement (); String sql = "Delete from emp2 where ename= ' lmdtx '"; int n = stmt.executeupdate (sql); SYSTEM.OUT.PRINTLN (n); Con.close ();}}

There will be a risk of being injected.

name:a '  or  ' b ' = ' ba '  or  ' b ' = ' bselect ename,empno, sal from emp2 where ename =  ' A '  or  ' b ' = ' B ' 

package jdbc;import java.sql.connection;import java.sql.drivermanager;import  java.sql.resultset;import java.sql.statement;import java.util.scanner;public class  Oracledemo04 {public static void main (String[] args)  throws Exception{ Scanner in = new scanner (system.in); System.out.print ("Name:"); String name = in.nextline (); SYSTEM.OUT.PRINTLN (name); Class.forName ("Oracle.jdbc.OracleDriver"); string url =  "Jdbc:oracle:thin: @x1. zongxuan.online:1521:xx"; string user =  "Scott"; string pass =  "xxxxx"; Connection con = drivermanager.getconnection (Url,user,pass); Statement stat = con.createstatement (); String sql = "select ename,empno,sal from emp2 where ename = " + Name+ "'"; SYSTEM.OUT.PRINTLN (SQL); Resultset rs = stat.executequery (SQL); while (Rs.next ()) {System.out.priNtln (rs.getstring (1) + "," +rs.getint (2) + "," +rs.getint (3));} Con.close ();}}

PreparedStatement interface (with this good)

Increase efficiency

Prevent SQL injection

1 Creating a connection

2PreparedStatement

PreparedStatement stmt = con.preparestatement (SQL)

3 stmt.setstring (1, "xxx");

Stmt.setint (1,123);

package jdbc;import java.io.bufferedreader;import java.io.inputstreamreader;import  java.sql.connection;import java.sql.drivermanager;import java.sql.preparedstatement;import  Java.sql.resultset;public class oracledemo05 {public static void main (String[]  args) Throws exception{bufferedreader br = new bufferedreader (new  InputStreamReader (system.in)); System.out.println ("Name:"); String name = br.readline (); Class.forName ("Oracle.jdbc.OracleDriver"); string url =  "Jdbc:oracle:thin: @x1. zongxuan.online:1521:xx"; string user =  "Scott"; string pass =  "xxxxx"; Connection con = drivermanager.getconnection (Url,user,pass); string sql =  "Select empno,ename,sal from emp2 where ename=?"; Preparedstatement stmt = con.preparestatement (SQL);//Set the value of the first question mark in  sql    to a string   StmT.setstring (1,name); Resultset rs = stmt.executequery (); while (Rs.next ()) {System.out.println (Rs.getint (1) + "," + Rs.getstring (2) + "," +rs.getint (3)); Con.close ();}}

Package Jdbc;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.preparedstatement;public Class OracleDemo06 {public static void main (string[] args) throws Exception{class.forname ("Oracle.jdbc.OracleDriver"); String url = "Jdbc:oracle:thin: @x1. zongxuan.online:1521:xx"; String user = "Scott"; String pass = "xxxxx"; Connection con = drivermanager.getconnection (url,user,pass); String sql = "INSERT into EMP2 (empno,ename) VALUES (?,?)"; PreparedStatement stmt = con.preparestatement (sql), int n =0;for (int i =1000;i<3000;i++) {stmt.setint (1, i+1); Stmt.setstring (2, "sting" +i); n = stmt.executeupdate (); System.out.println (i+ "," +n);} Con.close ();}}

Encapsulation Connection Tool Class

Properties

text files, which are stored in Key-valuse

Driver=oracle.jdbc.oracledriverurl=jdbc:oracle:thin: @x1. zongxuan.online:1521:xxuser=scottpass=xxxxx

package jdbc;import java.io.bufferedreader;import java.io.ioexception;import  java.io.inputstreamreader;import java.sql.connection;import java.sql.drivermanager;import  Java.util.properties;public class dbutils {private static string driver;private  static string url;private static string user;private static string  pass;static{properties props = new properties (); Try {props.load ( DBUtils.class.getClassLoader (). getResourceAsStream ("Jdbc/db.properties"));d River= props.getproperty (" Driver "), Url= props.getproperty (" url "), User= props.getproperty (" user ");p ass= props.getproperty ( "Pass"); Class.forName (driver);}  catch  (ioexception e)  {e.printstacktrace ();}  catch  (classnotfoundexception e)  {e.printstacktrace ();}} Public static connection openconnection ()  throws exception{return drIvermanager.getconnection (Url,user,pass);}} 

Package Jdbc;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.preparedstatement;import Java.sql.resultset;public class OracleDemo08 {public static void main (string[] args) throws Exception {Connection con = DB Utils.openconnection (); String sql = "Select empno,ename,sal from EMP2"; PreparedStatement stmt = con.preparestatement (sql);//sets the value of the first question mark in SQL to the string resultset rs = Stmt.executequery (); while (RS.N Ext ()) {System.out.println (Rs.getint (1) + "," + rs.getstring (2) + "," + rs.getint (3));} Con.close ();}}

Date

Package Jdbc;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.sqlexception;import Java.sql.date;public class Oracledemo09 {public static void main (string[] args) throws exception{connection con = dbutils. OpenConnection (); String sql = "INSERT into EMP2 (empno,ename,hiredate)" + "VALUES (?,?,?)"; PreparedStatement stmt =con.preparestatement (SQL) Stmt.setint (1, 4000); Stmt.setstring (2, "LMDTX"); Stmt.setdate (3, New Date (System.currenttimemillis ())); int n =stmt.executeupdate (); SYSTEM.OUT.PRINTLN (n); Con.close ();}}

Batch Processing

Batch processing of BULK INSERT updates

Stmt.addbatch ();

Add a batch buffer to the data you just set up

stmt

Perform batch processing to transfer buffered data to database execution at once

Cached batches are subject to crying justifying JVM memory limit to specify a reasonable batch value.

Package Jdbc;import Java.sql.connection;import Java.sql.preparedstatement;public class OracleDemo10 {public static void Main (string[] args) throws exception{connection con = dbutils.openconnection (); String sql = "INSERT into EMP2 (empno,ename) VALUES (?,?)"; PreparedStatement stmt = con.preparestatement (sql);//BULK INSERT for (int i =5000;i<=6000;i++) {stmt.setint (1, i); Stmt.setstring (2, "M" +i); Stmt.addbatch ();//Add Batch}stmt.executebatch ();//Execute Batch con.close ();}}

Package Jdbc;import Java.sql.connection;import Java.sql.preparedstatement;public class OracleDemo10 {public static void Main (string[] args) throws exception{connection con = dbutils.openconnection (); String sql = "INSERT into emp100 (id,name) VALUES (?,?)"; PreparedStatement stmt = con.preparestatement (sql);//BULK INSERT for (int i =1;i<=2000000;i++) {stmt.setint (1, i); Stmt.setstring (2, "M" +i); Stmt.addbatch ();//Add batch if (i%20000==0) {Stmt.executebatch ();}} Stmt.executebatch ();//Execute Batch con.close ();}}

JDBC's thing manipulation

All DML in JDBC will default to commit after each DML statement.

This article is from the "Romantic Laugh" blog, make sure to keep this source http://lmdtx.blog.51cto.com/6942028/1837387

Java38: Database Four (Oracle)