Java_jdbc_oracle Brief summary (2016-11-23)

JDBC Connecting instances of Oracle

Haven't written in a long time jdbc, basic forget clean, feel free to insert a diagram, simple study. And then do something else .....

Using JDBC to manipulate database steps is fixed

1. Import the driver package into the database, each database driver package is different, below I provide an Oracle database driver package http://download.csdn.net/detail/hncsy403/4530830 download it and put it in Lib in Web-inf in the Web project

2. Select Project Right, select Build Bath, add JARs in libraries, select just the jar package

Student Class Bean

Package Jdbc.bean;import Java.util.date;public class Student {private int id;private string name;private string password; Private String sex;private int age;private Date birthday;private string memo;private string photo;private date regtime;pub LIC int getage () {return age;} Public Date Getbirthday () {return birthday;} public int getId () {return ID;} Public String Getmemo () {return memo;} Public String GetName () {return name;} Public String GetPassword () {return password;} Public String Getphoto () {return photo;} Public Date Getregtime () {return regtime;} Public String Getsex () {return sex;} public void Setage (int.) {this.age = age;} public void Setbirthday (Date birthday) {this.birthday = birthday;} public void setId (int id) {this.id = ID;} public void Setmemo (String memo) {This.memo = Memo;} public void SetName (String name) {this.name = name;} public void SetPassword (String password) {this.password = password;} public void Setphoto (String photo) {This.photo = photo;} public void Setregtime (Date RegTIME) {this.regtime = Regtime;} public void Setsex (String sex) {this.sex = sex;}}

Tool class: Dbutil

Package Jdbc.util;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;public class Dbutil {private static final String Driverclass = " Oracle.jdbc.driver.OracleDriver ";p rivate static final String Jdbcurl =" Jdbc:oracle:thin: @liumo: 1521:orcl ";p rivate Static final String user = "Test_lm";p rivate static final string password = "TEST_LM";p ublic static Connection Getconn () { 1. Register the drive try {class.forname (driverclass);} catch (ClassNotFoundException e) {e.printstacktrace ();} 2. Create Connection (Database Connection object) Connection conn = null;try {conn = drivermanager.getconnection (jdbcurl, user, password); Conn.setautocommit (FALSE); return conn;} catch (SQLException e) {e.printstacktrace ();} /* * Connection is Statement's factory, a connection can produce multiple statement. * Statement is the factory of resultset, and a statement can only correspond to one resultset (they are one by one corresponding relationships). * So in a program to use multiple resultset, you must again connection to get multiple statement, and then a statement corresponding to a resultset. */return null;} /** * Close Connection (database connection object) * @pAram Conn */public static void Close (Connection conn) {try {if (conn! = null) {Conn.close ();}} catch (SQLException e) {E.P Rintstacktrace ();}} /** * Close the compiled SQL statement object * @param stmt */public static void Close (Statement stmt) {try {if (stmt! = null) {Stmt.close ();}} CA TCH (SQLException e) {e.printstacktrace ();}} /** * Close Result set * @param rs */public static void Close (ResultSet rs) {try {if (rs! = null) {Rs.close ();}} catch (SQLException e) {e.printstacktrace ();}} /** * COMMIT TRANSACTION * @param conn */public static void commit (Connection conn) {try {if (conn! = null) {Conn.commit ();}} catch (SQL Exception e) {e.printstacktrace ();}} /** * ROLLBACK TRANSACTION * @param conn */public static void rollback (Connection conn) {try {if (conn! = null) {Conn.rollback ();}} catch (SQLException e) {E.printstacktrace ();}}}

The actual Dao:studentdao

　

Package Jdbc.dao;import Java.io.fileinputstream;import Java.io.filenotfoundexception;import Java.io.fileoutputstream;import Java.io.ioexception;import Java.io.inputstream;import Java.io.OutputStream;import Java.sql.connection;import Java.sql.preparedstatement;import Java.sql.resultset;import java.sql.SQLException; Import Java.util.arraylist;import java.util.list;import Jdbc.bean.student;import Jdbc.util.dbutil;public class Studentdao {/** * save * @param student */public void Save (Student student) {Connection conn = Dbutil.getconn (); PreparedStatement pstmt = null; String sql = "INSERT into t_student (name,password,sex,age,birthday,memo,photo,reg_time)"; SQL + = "VALUES (?,?,?,?,?,?,? ,?) try {pstmt = conn.preparestatement (sql);p stmt.setstring (1, Student.getname ());p stmt.setstring (2, Student.getpassword ());p stmt.setstring (3, Student.getsex ());p Stmt.setint (4, Student.getage ());p stmt.setdate (5, New Java.sql.Date (Student.getbirthday (). GetTime ())); Only the date of this form pstmt.setstring (6, Student.getmemo()); try {//build an input stream Blobpstmt.setblob (7, New FileInputStream (Student.getphoto ()));} catch (FileNotFoundException e) { E.printstacktrace ();} Pstmt.settimestamp (8, New Java.sql.Timestamp (Student.getregtime (). GetTime ())); Full time Format pstmt.executeupdate ();D butil.commit (conn);} catch (SQLException e) {dbutil.rollback (conn); E.printstacktrace ();} finally {Dbutil.close (pstmt);D Butil.close (conn) ;}} /** * Delete * @param id */public void Delete (int id) {Connection conn = Dbutil.getconn (); PreparedStatement pstmt = null; String sql = "Delete from t_student where id=?"; try {pstmt = conn.preparestatement (sql);p Stmt.setint (1, id);p stmt.executeupdate ();D butil.commit (conn);} catch ( SQLException e) {dbutil.rollback (conn); E.printstacktrace ();} finally {Dbutil.close (pstmt);D butil.close (conn);}} /** * Bulk Delete * @param ids */public void Deletebatch (int[] IDs) {Connection conn = Dbutil.getconn (); PreparedStatement pstmt = null; String sql = "Delete from t_student where id=?"; try {pstmt = conn.preparestatement (sql); for (iNT Id:ids) {Pstmt.setint (1, id);p stmt.addbatch ();} Pstmt.executebatch ();D butil.commit (conn);} catch (SQLException e) {dbutil.rollback (conn); E.printstacktrace ();} finally {Dbutil.close (pstmt);D Butil.close (conn) ;}} /** * Modify * @param student */public void update (student student) {Connection conn = Dbutil.getconn (); PreparedStatement pstmt = null;  String sql = "Update t_student set name=?,password=?,sex=?,age=?,birthday=?,memo=?,photo=?,reg_time=?" where id=? "; try {pstmt = conn.preparestatement (sql);p stmt.setstring (1, Student.getname ());p stmt.setstring (2, Student.getpassword ());p stmt.setstring (3, Student.getsex ());p Stmt.setint (4, Student.getage ());p stmt.setdate (5, New Java.sql.Date (Student.getbirthday (). GetTime ())); Only the date of this form pstmt.setstring (6, Student.getmemo ()); try {//constructs an input stream Blobpstmt.setblob (7, New FileInputStream ( Student.getphoto ()));} catch (FileNotFoundException e) {e.printstacktrace ();} Pstmt.settimestamp (8, New Java.sql.Timestamp (Student.getregtime (). GetTime ())); Full time format pstmt. Setint (9, Student.getid ());p stmt.executeupdate ();D butil.commit (conn); catch (SQLException e) {dbutil.rollback (conn); E.printstacktrace ();} finally {Dbutil.close (pstmt);D Butil.close (conn) ;}} /** * Find * @param ID * @return */public Student find (int id) {Connection conn = Dbutil.getconn (); PreparedStatement pstmt = null; ResultSet ResultSet = null; String sql = "SELECT * from T_student where id=?"; Student Student = null;try {pstmt = conn.preparestatement (sql);p Stmt.setint (1, id); resultSet = Pstmt.executequery (); if ( Resultset.next ()) {student = new student (); Student.setid (Resultset.getint ("id")); Student.setname ( Resultset.getstring ("name")); Student.setage (Resultset.getint ("Age")); Student.setbirthday (Resultset.getdate (" Birthday ") Student.setmemo (Resultset.getstring (" Memo ")); Student.setpassword (resultset.getstring (" password ")); Student.setregtime (Resultset.gettimestamp ("Reg_time")); Student.setsex (resultset.getstring ("Sex")); InputStream in = Resultset.getblob ("Photo"). Getbinarystream (); String PAth = "D:\\ltf.jpg"; try {outputstream out = new FileOutputStream (path); Copy (in, out); Out.close ();} catch ( FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} Student.setphoto (path);}} catch (SQLException e) {e.printstacktrace ();} finally {Dbutil.close (resultSet);D butil.close (pstmt);D Butil.close ( conn);} return student;} private void Copy (InputStream in, outputstream out) {int i = 0;try {while ((i = In.read ())! =-1) {out.write (i);} Out.flush ();} catch (IOException e) {e.printstacktrace ();}} /** * Query multiple records * @return */public list<student> query () {Connection conn = Dbutil.getconn (); PreparedStatement pstmt = null; ResultSet ResultSet = null; String sql = "SELECT * from T_student"; list<student> studentlist = new arraylist<student> (); try {pstmt = conn.preparestatement (sql); ResultSet = Pstmt.executequery (); while (Resultset.next ()) {Student Student = new Student (); Student.setid (Resultset.getint ("id")) ; Student.setname (resultset.getstring ("NamE ")); Student.setage (Resultset.getint (" Age ")); Student.setbirthday (Resultset.getdate (" Birthday ")); Student.setmemo (Resultset.getstring ("Memo")); Student.setpassword (resultset.getstring ("password")); Student.setregtime (Resultset.gettimestamp ("Reg_time")); Student.setsex (resultset.getstring ("Sex")); InputStream in = Resultset.getblob ("Photo"). Getbinarystream (); String Path = "D:\\ltf.jpg"; try {//Put the picture stored in the database to a location on the disk outputstream out = new FileOutputStream (path); Ut.close ();} catch (FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} Student.setphoto (path); Studentlist.add (student);}} catch (SQLException e) {e.printstacktrace ();} finally {Dbutil.close (resultSet);D butil.close (pstmt);D Butil.close ( conn);} return studentlist;}}

Test class: Daotest

package jdbc.test;import static Org.junit.assert.fail;import Java.text.parseexception;import Java.text.simpledateformat;import Java.util.date;import Jdbc.bean.Student;import       Jdbc.dao.studentdao;import Org.junit.test;public class Daotest {@Testpublic void Test () {fail ("not yet implemented");        Student s=new Student ();        S.setid (3);        S.setname ("ZSS");        S.setpassword ("ZSS");        S.setsex ("female");        S.setage (21);        try {s.setbirthday (new SimpleDateFormat ("Yyyy-mm-dd"). Parse ("1980-01-01"));        } catch (ParseException e) {//TODO auto-generated catch block E.printstacktrace ();        } s.setmemo ("I'm Good");        S.setphoto ("c:\\ltf.jpg");                 S.setregtime (New Date ());        Studentdao sd=new Studentdao ();        Sd.save (s);        Sd.update (s);        Sd.delete (1);        Sd.deletebatch (New int[]{2,3}); System.out.println (Sd.query (). Size ());}} 

　　

Each SQL operation needs to establish and close the connection, which is bound to consume a lot of resource overhead, how to avoid

Analysis: You can use a connection pool to maintain a unified connection without having to build and close each time. In fact, this is a lot of the tools used to encapsulate JDBC. (When you see hibernate,spring connected to the database and the transaction, look at it.) ）

Reference:

Simple additions and deletions via JDBC (take MySQL for example) http://www.cnblogs.com/wuyuegb2312/p/3872607.html

JDBC Learning-How to get connection http://blog.csdn.net/luohuacanyue/article/details/8770450

Java_jdbc_oracle Brief summary (2016-11-23)