JDBC: Metadata && Get primary key value of Insert record && _JDBC_ processing Blob

Source: Internet
Author: User

I. Meta data

DatabaseMetaData class

Many methods are available in the DatabaseMetaData class to obtain various information about the data source, which provides a very detailed understanding of the database information:
GetURL (): Returns a String class object that represents the URL of the database.
GetUserName (): Returns the user name that is connected to the current database management system.
IsReadOnly (): Returns a Boolean value that indicates whether the database allows only read operations.
Getdatabaseproductname (): Returns the product name of the database.
Getdatabaseproductversion (): Returns the version number of the database.
Getdrivername (): Returns the name of the driver driver.
Getdriverversion (): Returns the version number of the driver.

ResultSetMetaData class

An object that can be used to obtain information about the type and properties of a column in a ResultSet object:
getColumnName (int column): Gets the name of the specified column
getColumnCount (): Returns the number of columns in the current ResultSet object.
Getcolumntypename (int column): Retrieves the database-specific type name of the specified column.
getcolumndisplaysize (int column): Indicates the maximum standard width of the specified column, in characters.
isnullable (int column): Indicates whether the value in the specified column can be null.
isautoincrement (int column): Indicates whether the specified column is automatically numbered so that the columns are still read-only.  

<span style= "FONT-SIZE:18PX;" >public class Testdatabasemetedata {/** * ResultSetMetaData describes the metadata of the result set * You can get the columns in the result sets, the column name (or alias) * Combined with reflection can write a common query method */@Test public void Testresultsetmetadata () {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {conn = Jdbc_tools.getconnection (); String sql = "SELECT * from students";p s = conn.preparestatement (sql); rs = Ps.executequery (); ResultSetMetaData RSMD = Rs.getmetadata ();//Gets the number of columns int columnCount = Rsmd.getcolumncount (); System.out.println (ColumnCount); for (int i = 0;i<columncount;i++) {//Get column name string columnName = Rsmd.getcatalogname (i + 1);//Gets the alias of the column string ColumnLabel = Rsmd.getcolumnlabel (i + 1); System.out.println (columnlabel+ ":" +columnname);}} catch (Exception e) {e.printstacktrace ();} Finally{jdbc_tools.relasesource (Rs,conn, PS);}} /** * Databasemrtadata is a metadata object that describes the database * can be obtained by Connection */@Testpublic void Test () {Connection conn = Null;databasemetadata D ATA = NULL; ResultSet rs = null;try {conn = jdbc_tools.getconnection ();d ATA = CoNn.getmetadata ();//Gets the database version number int version = Data.getdatabasemajorversion (); SYSTEM.OUT.PRINTLN (version);//Gets the user name that is connected to the database string, Data.getusername (); SYSTEM.OUT.PRINTLN (user);//Gets the database to which the database is connected rs = Data.getcatalogs (); while (Rs.next ()) {System.out.println ( Rs.getstring (1));}} catch (Exception e) {e.printstacktrace ();} JDBC_TOOLS.RELASESOURCE (conn, null);}} </span>

Second, obtain the database auto-generated primary key


<span style= "FONT-SIZE:18PX;" >/** * Get MySQL database automatically generated primary key * Oracle does not */@Testpublic void Test () {Connection conn = null; PreparedStatement PS = null;try {conn = Jdbc_tools.getconnection (); String sql = "INSERT into customer (name, email, birth)" + "VALUES (?,?,?)"; /Use the overloaded PreparedStatement (sql,flag) PS = conn.preparestatement (sql, Statement.return_generated_keys);p s.setstring (1, "a");p s.setstring (2, "[email protected]");p s.setdate (3, New Date (New Java.util.Date (). GetTime ());p s = Conn.preparestatement (SQL);p s.executeupdate ()//through Preparedstatement.getgeneratedkeys () gets//ResultSet object containing the newly generated primary key (Only one column of Generated_keys to hold the newly generated primary key value) ResultSet rs = Ps.getgeneratedkeys (); if (Rs.next ()) {System.out.println (Rs.getobject (1));} ResultSetMetaData RSMD = Rs.getmetadata (); for (int i = 0;i<rsmd.getcolumncount (); i++) {System.out.println ( Rsmd.getcolumnname (i + 1));}} catch (Exception e) {e.printstacktrace ();}} </span>
third, Oracle LOB

LOB, or large Objects (large object), is a data type used to store large amounts of binary and textual data (a LOB field can store up to 4GB of data).
There are two types of lobs: internal lob and external lob.
An internal LOB stores data in the form of a byte stream inside the database. As a result, many operations on internal lobs can participate in transactions, or they can be backed up and restored as normal data. Oracle supports three types of internal lobs:
BLOB (binary data)
CLOB (Single-byte character data)
NCLOB (multibyte character data).
The CLOB and NCLOB types are suitable for storing very long text data, and BLOB word MSA is used to store large amounts of binary data, such as video, audio, files, and so on.
Only one external LOB type, the bfile type, is currently supported. Within a database, this type stores only the location information of the data in the operating system, while the entity of the data exists in the file system of the operating system as an external file. Thus, the data represented by the type is read-only and does not participate in transactions. This type helps users to manage a large number of files accessed by external programs.

MySQL BLOB Type description

In MySQL, a blob is a binary large object, a container that can store large amounts of data, which can accommodate different sizes of data.
MySQL four types of blobs (except for the maximum amount of information stored, they are equivalent)

In practice, you define different blob types based on the size of the data you need to deposit. It is important to note that if the stored file is too large, the performance of the database will degrade.

Steps:

1. Insert empty blob INSERT into javatest (name,content) VALUES (?, Empty_blob ());
2. Get the cursor for BLOB select content from Javatest where name=? for update; Note: You must add a for update to lock the row until the row has been modified to ensure that no concurrency conflicts occur.
3, using IO, and get to the cursor to the database to write data flow

Demonstrate LOB read and write

<span style= "FONT-SIZE:18PX;" >/** * Data inserted into BLOB type must use PreparedStatement because data of blob type cannot be spelled with string */@Testpublic void Test () {Connection conn = null; PreparedStatement PS = null;try {/** * Blob write operation */conn = Jdbc_tools.getconnection (); String sql = "INSERT INTO Temp (IMG) VALUES (?)"; PS = conn.preparestatement (sql);p S.setblob (1, New FileInputStream ("C://users//kevy//desktop//1.png")); Ps.executeupdate ();/** * Use GetBlob ()  to read BLOB data * Call the Blob's Getbinarystream () method to get the input stream, then io ResultSet rs = ps.executequer Y (); if (Rs.next ()) {Blob picture = Rs.getblob (1), InputStream in = Picture.getbinarystream (), outputstream out = new FileOutputStream ("C://users//kevy//desktop//11.png"); byte[] buf = new Byte[1024];int len = 0;while (len = In.read (BUF)) !=-1) {out.write (buf, 0, Len);} Out.close (); In.close ();} */} catch (Exception e) {e.printstacktrace ();}} </span>

JDBC: Metadata && get primary key values for inserted records && _JDBC_ processing blobs

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.