java--Database Operations

There are several drivers for 1.JDBC

1>jdbc-odbc

relies on ODBC drives and database traffic to load ODBC binaries onto clients that use the driver

2> Local API

To convert a JDBC call on the client's API to a call to a oracle,db2 or other DBMS

3>JDBC Network Driver

Translates JDBC into a DBMS-independent network protocol and is converted to a DBMS protocol by a server.

4> Local Protocol Driver

Converts JDBC directly to the network protocol used by the DBMS, allowing direct invocation of the DBMS server on the client

2. Stored Procedures

Calling a stored procedure with no parameters

CREATE PROCEDURE insertprocedure as begin INSERT into TB values ("); end;

Drivermanager.getconnection (). Preparecall ("{Call Insertprocedure ()}");//Calling stored procedure

To invoke a stored procedure with parameters

CREATE PROCEDURE Validateselect

@userName varchar (20)

As SELECT * from TB where [email protected]

Drivermanager.getconnection (). Preparecall ("{Call Validateselect (' Mr ')}"); Invoke stored procedure

3.

Data: Basic objects for database storage, including digital text graphics images, etc.

Database: A collection of data that is organized and shared

Database management System: Core software of database system, operate under the support of operating system. Data definition, data operation, operation Management, building maintenance, etc.

Database system: After the introduction of the database system, including the database, database management system

4.Statement and PreparedStatement differences

Statement is used to execute static SQL and must be prepared in advance.

PreparedStatement means precompilation, parsing compilation, and placing command buffers. When running the same PreparedStatement object, only parsing does not compile, reducing compilation times

5. Transaction processing

Setautocommit () Transaction Auto-commit

Rollback () transaction rollback

Commit () Transaction manual Commit

6. Get the table structure

Connection----createstatement ()----executeQuery ()----

GetMetaData ()----getcolumncount ()----

getColumnName (i)

Getcolumntypename (i)

ResultSetMetaData getting information about the types and properties listed in the ResultSet object

ResultSet Save the result set of the query data

7. Database storage Image

SQL Server:image

Mysql:blob Img=rs.getblob ("Picture")---new Image (img.getbytes (1, (int) img.length ()))

In=fileinputstream (New File (filepath))

Setbinarystream (2,in, (int) new File (filepath). Length ())

8.SQL optimization

1> primary key length not too long

2> fixed-length character fields should use char or nchar

3> length not fixed----varchar or nvarchar

4> should give a default value to the optional fields

5> indexing fields with high query frequency

6> Avoid using like,in,exists,not,<>,!>,!<, etc.

7> avoid using functions in where

8> joins and subqueries are used, preference is given to join

9> less with Distinct,order by, Union query union ALL Less Union

10> using stored procedures as much as possible

9.MYSQL Chinese garbled

1> set the data table and its fields to encode Utf-8

2> Specifies the parameters of the data connection string, specifying that the SQL statement sent to the MySQL database server is encoded as Utf-8

10.statement----connection.createstatement ()

Statement.executeupdate () Statement.executequery ()

Insert into table (Name,price) VALUES (",") while (Rs.next ()) Rs.getint (1), Rs.getfloat (3), Rs.get String (). Trim ()

11. Storing and reading databases in plain text

Deposit:

The column in the data table that stores the large text file is set to the text type, Filechooser.getselectedfile (). GetAbsolutePath ()

Setfilefilter (Filter)

Insert into Tb_text (text) values (?)

Connecttion.preparestatement (SQL). Setasciistream (1,in, (int) file.length ()). Executeupdate ()

Stream data to a database, which can be either a Java stream object or a subclass that implements a standard interface

Read:

Select text from Tb_text

Conn.preparestatement (SQL). ExecuteQuery (). Next (). Getasciistream (1)

ResultSet InputStream In--bufferreader (New InputStreamReader (in))----readLine ()

12. Get the database so the table

DatabaseMetaData object in JDBC, Gettables (the category name to retrieve, the name of the schema, the name of the table to retrieve, the category of the table type)

Connection.getmetadata ()----gettables (null,null, "%", {"table"})----rs.getstring (3) User table name

DatabaseMetaData ResultSet

Oracle:select table_name from User_tables

13. How to implement MySQL backup and recovery

Backup: mysqldump-----Runtime class input cmd command, execute mysqldump

String back= "Mysqldump-u" +username+ "-P" +password+ "" +database;

Process p=runtime.getruntime (). EXEC ("cmd.exe/c" +back);

New BufferedReader (New InputStreamReader (P.getinputstream (), "Utf-8"))--readline

New OutputStreamWriter (path), "Utf-8")---writer (sb.tostring ())

Restore: Reads the SQL statement from the backup file and uses the runtime class to enter the cmd command into the current environment and execute the MySQL command

String back= "Mysqldump-u" +username+ "-P" +password+ "" +database;

Process p=runtime.getruntime (). EXEC ("cmd.exe/c" +back);

P.getoutputsream ()

New BufferedReader (New InputStreamReader (New FileInputStream (PT), "Utf-8"))----readLine

New BufferedWriter (New OutputStreamWriter (P.getoutputstream (), "Utf-8"))

14. Multi-Table Connection query

B Public Part C

1> equivalent connection query (internal connection) table1 INNER join table2 on

Only all matching rows, including duplicate columns, are returned, only queries are available----C

2> Outer joins (left joins, right joins) Table1 right join table2 on

Extension of internal connections, query integrity, no loss of data

Left Join:a+c

Right Join:b+c

3> fully connected--full join

A+b, non-qualifying data, the left and right table corresponding column filled with null values

(UNION all retains duplicate columns) SELECT * FROM tb_dept Union SELECT * FROM TB_DEPT1

15. Date Storage

DateTime of the Java date--> database

Conn.preparestatement ()---settimestamp (1,new Timestamp (Format.parse (String). GetTime ())----executeupdate ()

Conn.createstatement (). ExecuteQuery ("SELECT * from Tb_date")----GetMetaData (). getColumnCount ()

New Timestamp (Rs.getdate (1). GetTime ()). ToString (). subString (0,10)

Oracle:todate

SQL Server:convert

java--Database Operations