JDBC Technology Foundation Finishing

MySQL Startup and shutdown commands:
Startup: net start MySQL shutdown: net stop MySQL
mysql command to change password:
Use MySQL;
Update user set Password=password (' 12345 ') where user= ' root ';

command to log in:
Mysql-u root-p

New Login User
mysql> INSERT INTO Mysql.user (Host,user<password) VALUES ("localhost", "Test", Password ("123456"))

2. Authorizing the user

Authorization format: Grant permissions on database. * To User name @ login host identified by "password";

2.1 Log in to MySQL (rooted), log in as root here:

@>mysql-u root-p

@> Password

2.2 First create a database for the user (TestDB):

Mysql>create database TestDB;

2.3 Authorization test The user has all permissions for the TestDB database (all permissions for a database):

Mysql>grant all privileges in testdb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges;//Refresh System Permissions Table

Format: Grant permissions on database. * To User name @ login host identified by "password";

2.4 If you want to specify a partial permission to a user, you can write:

Mysql>grant select,update on testdb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges; Refresh System Permissions Table

2.5 Authorization test The user has some permissions for all databases:

Mysql>grant Select,delete,update,create,drop On * * to [e-mail protected] "%" identified by "1234";

The test user has Select,delete,update,create,drop permissions on all databases.

@ "%" indicates authorization for all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1, if set to the real local address, do not know whether it can, no authentication.) ）

Authorization to localhost: plus a grant all privileges on testdb.* to [email protected] identified by ' 1234 ';

3. Delete a user

@>mysql-u root-p

@> Password

Mysql>delete from user Where user= ' test ' and host= ' localhost ';

Mysql>flush privileges;

Mysql>drop database TestDB; Delete a user's database

Delete account and permissions: >drop user username @ '% ';

>drop user username @ localhost;

4. Modify the specified user password

@>mysql-u root-p

@> Password

Mysql>update Mysql.user Set Password=password (' New password ') where user= "test" and host= "localhost";

Mysql>flush privileges;

5. List all databases

Mysql>show database;

6. Switching the database

Mysql>use ' database name ';

7. List all Tables

Mysql>show tables;

8. Display the data table structure

Mysql>describe table name;

9. Deleting databases and data tables

Mysql>drop database name;

Mysql>drop table Data Sheet name;

Jdbc

The process of JDBC connecting to a database

① Registration Database Driver

Example: Class.forName ("Com.mysql.jdbc.Driver");

② building a database connection URL

Example: URL basic format: "JDBC protocol +IP address or domain name + port + database name"
"Jdbc:mysql://localhost:3306/test"

③ Getting Connection objects
Drivermanager.getconnection (Url,username,password);

The connection code is as follows:

try{
Class.forName ("Com.mysql.jdbc.Driver");//Load Database driver, register to drive manager
String url = "Jdbc:mysql://localhost:3306/testdatabase";//Database connection string
String username = "Test";//Database user name
String password = "123456";//Database Password
Connection conn = drivermanager.getconnection (Url,username,password);//Create Connection Connection
if (conn!=null) {//Determines whether the connection object is empty
Out.print ("Database connection succeeded! ");//NOT NULL output" database connection succeeded "
Conn.close ();//close connection
}else{
Out.print ("Database connection Failed");//Output database connection failed
}

}catch (ClassNotFoundException e) {
E.printstacktrace ();
}catch (SQLException e) {
E.printstacktrace ();
}




JDBC API:

Method declarations and descriptions for connection interfaces
1. void close () throws SQLException
Immediately releases the JDBC resource occupied by the database connection of the connection object, which should be called immediately after the database is manipulated

2.void commit () throws SQLException
Commits the transaction and frees all the database locks currently held by the connection object, and calls the method to commit the transaction when the transaction is set to manual commit mode

3.Statement createstatement () throws SQLException
Create a statement object to send the SQL statement to the database, which returns the statement object

4.boolean Getautocommint () throws SQLException
Used to determine whether the connection object is set to Autocommit mode, and the method returns a Boolean value

5.DatabaseMetaData Getmetdata () throws SQLException
Gets the element DatabaseMetaData object for the database to which the connection object is connected, including information about the database's tables, supported SQL syntax, stored procedures, this link function, and so on

6.int gettransactionisolation () throws SQLException
Gets the current thing isolation level of the Connection object

7. Boolean isclosa () throws SQLException
To determine if the connection object is disconnected from the database, the method returns a Boolean value, and it is important to note that if the connection object is disconnected from the database,
You can no longer manipulate the database through the Connection object

8.boolean IsReadOnly () throws SQLException

Determines whether the connection object is read-only and returns a Boolean value

9.PreparedStatement preparedstatement (String sql) throws SQLException
The parameterized SQL statement is precompiled and stored in the PreparedStatement object, and the common PreparedStatement object is returned

10.void releasesavepoint (SavePoint savepoint) throws SQLException
Removes the specified savepoint and subsequent SavePoint objects from the current thing

11.void rollback () throws SQLException
Roll back the thing and release all the database locks currently held by the connection object, noting that the method needs to be applied to the Connection object's manual commit mode

12.void Setautocommit (Boolean autocommit) throws SQLException
Sets the commit mode of the connection object, if the value of the parameter autocmmit is set to True,connection object is autocommit mode, if the value of the parameter autocmmit is set to False,
The connection object is a manual commit mode

13.void setreadonly (Boolean ReadOnly) throws SQLException
Sets the connection mode of the connection object to read-only, which is used to optimize the database

14.Savepoint Setsavepoint () throws SQLException
Creates an unnamed hold point in the current thing and returns the object that holds the point

15.Savepoint setsavepoint (String name) throws SQLException
Creates a reserved point for the specified name in the current thing and returns the object of the reserved point

16.void settransactionisolation (int level) throws SQLException
Set the Thing isolation level for a Connection object



2.DriverManager class
1.public static void Deregisterdriver (Driver Driver) throws SQLException
Removes a driver from DriverManager's management list, with parameter driver as the drive object to be deleted

2.public static Connection getconnection (String url) throws SQLException
Based on the specified database connection URL, the CV is connected to the database connection, and the parameter URL is the database connection URL

3.public static Connection getconnection (String url,properties info) throws SQLException
Establishes the database connection connection based on the specified database connection URL and database connection property information. Parameter URL is database URL, parameter info is database connection property

4.public static Connection getconnection (String url,string user,string password) throws SQLException
Establishes the database connection connection based on the specified database connection URL user name and password. The parameter URL is the database URL, the user name of the database connection database
Parameter password is the password to connect to the database

5.public Static Enumeration<driver>getdriver ()
Gets all the drivers that have been loaded in the current DriverManager, and his return value is enumeration

6.public static void Registerdriver (Driver Driver) throws SQLException
Box DriverManager register a drive object, parameter driver to register the driver


3.Statement interface
Statement method and description of the interface

1.void aadbatch (String sql) throws SQLException
Adds an SQL statement to the current command list of the statement object, which is used for batch processing of SQL commands

2.void Clearbatch () throws SQLException
Clears the list of commands in the statement object

3.void Close () throws SQLException
Immediately frees the database and JDBC resources for the statement object, rather than waiting for the object to close when it has been closed

4. Boolean execute (Sting sql) throws SQLException
Executes the specified SQL statement, returns TRUE if the SQL statement returns a result, and returns false instead

5.int[]executebatch () throws SQLException
Submit a batch of SQL commands to the database for execution, returning an array of newer technologies

6.ResultSet Execute Query (String sql) throws SQLException
Executes a query type (select) De SQL statement that returns the result set Resuktset object obtained by the query

7.executeupdate int executeupdate (String sql) throws SQLException
SQL statement that executes the DML type (insert uodate delete) in the SQL statement, returning the number of rows affected by the update

8.Connection getconnection () throws SQLException
Gets the connection that generated the statement object

9.boolean Isclosa () throws SQLException
Determines whether the statement object is closed and, if closed, no longer calls the statement object to execute the SQL statement, which returns a Boolean value



4. PreparedStatement interface
PreparedStatement method and description of the interface
1.void setbinarystream (int paramenterindex,inputstream x) throws SQLException
The input stream x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

2.void SetBoolean (int paramenterindex, Boolean x) throws SQLException
Use Boolean x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

3.void setByte (int paramenterindex, byte x) throws SQLException
The byte value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

4.void setDate (int paramenterindex, Date x) throws SQLException
Java.sql.Date value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

5.void setdouble (int paramenterindex, Double x) throws SQLException
The double value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

6.void setfloat (int paramenterindex, Float x) throws SQLException
Float value x As the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

7.void setint (int paramenterindex, int x) throws SQLException
The int value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

8.void setint (int paramenterindex, long x) throws SQLException
The Long value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

9.void setobject (int paramenterindex, Object x) throws SQLException
Objiect object x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

10.void setshort (int paramenterindex, short x) throws SQLException
The short value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

11.void setString (int paramenterindex, String x) throws SQLException
String value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position

12.void settimestamp (int paramenterindex, Timestamp x) throws SQLException
Timestamp value x as the parameter value in the SQL statement, Paramenterindex as the index of the parameter position



5.ResultSet interface
ResultSet method and description of the interface

1.boolean Absolute (int row) throws SQLException
Moves the cursor to the ResultSet object given the navigation number, the parameter row is the navigation number

2.void Afterlast () throws SQLException
After you move the cursor to the last line of the ResultSet object, the method is invalid if the result set contains no rows

3.void Beforefirst () throws SQLException
Immediately releases the database and JDBC resources for the ResultSet object

4.void Detelerow () throws SQLException
Remove the forward from the ResultSet object and the underlying database

5.boolean first () throws SQLException
Move the cursor to the first line of the ResultSet object

6.InputStream Getbinarystream (String columnlabel) throws SQLException
Gets the value of the specified column in the current row of the ResultSet object as a byte stream, with the parameter ColumnLabel as the name of the column

7.Date getDate (String columnlabel) throws SQLException
Gets the value of the specified column in the current row of the ResultSet object in a java.sql.Date way, with the parameter ColumnLabel as the name of the column

8.doublee getdouble (String columnlabel) throws SQLException
Gets the value of the specified column in the current row of the ResultSet object in double mode, with the parameter ColumnLabel as the name of the column

9.float getfloat (String columnlabel) throws SQLException
Gets the value of the specified column in the current row of the ResultSet object as a float, with the parameter ColumnLabel as the name of the column

10.int getInt (String columnlabel) throws SQLException
Gets the value of the specified column in the current row of the ResultSet object as an int, with the parameter ColumnLabel as the name of the column

11.String getString (String columnlabel) throws SQLException
Gets the value of the specified column in the current row of the ResultSet object as a string, with the parameter ColumnLabel as the name of the column

12.boolean Isclose () throws SQLException
Determines whether the current ResultSet object is closed

13.boolean last () throws SQLException
Move the cursor to the last line of the ResultSet object

14.boolean next () throws SQLException
Moves the cursor position backward one line, or false after meals if the moved new navigation valid return value is True

15.boolean Previous () throws SQLException
Moves the cursor position forward one line, such as moving a new row effectively returns true, otherwise returns false

JDBC Technology Foundation Finishing