Java Database programming

1. Introduction to relational database

The relational model is proposed by Codd, which is a data logical model which is based on the relationship between data and data.

A relational database is usually made up of one or more table objects, and all data in the database is stored in those tables. All tables have a table name that contains rows and columns, and each column contains the name, data type, and other properties of the column. Each row contains a specific value for a column.

2,sql statements

　　Create a table

Example, create a student table that contains the name and age.

CREATE table if not EXISTS student (

ID int primary KEY auto_increment,

Stu_name varchar (20),

Stu_age Samllint

);

Where if not exists is the protection statement, if there is a student table exists also will not error. The primary key is the primary key. Auto_increment the primary key from the growth.

Delete Table ( Note: No delete,delete is used here to delete data. )

drop table student;

　　Querying data

Example: SELECT * from student; (Querying all data in the student table)

Select name from student; (Query the data in the Student Table Name column)

SELECT * FROM student where age>20; (query data for people older than 20 in the student table)

Inserting data into a table

Example: There is student Zhangsan, age is 18

INSERT into student (Name,age) VALUES (' Zhangsan ', 18);

3,java Database Connection

Introduction to JDBC
JDBC is the short name of Java database connection, and it is a kind of Java-implemented data interface technology.
JDBC is made up of two layers.
The above layer is the JDBC API, which is responsible for communicating with Java applications and providing data to Java applications (Java applications manage the JDBC driver through the related classes provided in JDBC).
The following layer is the JDBC Driver API, which is primarily responsible for connecting to specific data environments.

：

Connect to the database using JDBC
set up a data source
Loads the driver for the database used by the Java application. Here you can choose one of the three ways mentioned above.
Establish a connection
The standard way to establish a connection to a database is to invoke the method:
Drivermanger.getconnection (String url,string user,string password).
The Drivermanger class is used to process driver incoming and to provide support for new database connections
Execute SQL statement

Package com.wode.test;

Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;

public class Test01 {
public static void Main (string[] args) {
Properties Properties=new properties ();

try {
Properties.load (New FileInputStream ("./src/user.properties"));
String name=properties.getproperty ("name");
String pwd=properties.getproperty ("pwd");
String url=properties.getproperty ("url");
Class.forName ("Com.mysql.jdbc.Driver");//Get MySQL driver through reflection
Connection con=drivermanager.getconnection (URL,NAME,PWD);//Connect to the database through the drive manager
String sql= "SELECT * from student";//Prepare SQL statement
Statement st=con.createstatement ();//Send SQL statement
ResultSet rst=st.executequery (SQL);//Receive result set
while (Rst.next ()) {
String username=rst.getstring ("name");
System.out.println (UserName);
}
} catch (ClassNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (FileNotFoundException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

DriverManager
The DriverManager class is the management layer of JDBC, which acts between the user and the driver. It establishes a connection between the database and the corresponding driver
The DriverManager class contains a list of Driver classes that have registered themselves by calling the method Drivermanager.registerdriver
Example: Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
String url = "Jdbc:odbc:company";
Connection con=
drivermanager.getconnection (URL, "UserID", "passwd");
Statement
The Statement object is used to send SQL statements to the database
Example:
Connection con = drivermanager.getconnection (URL, "UserID", "passwd");
Statement stmt = Con.createstatement ();
ResultSet rs = stmt.executequery ("Select a, B, C from Table2");
ResultSet
ResultSet contains all rows that conform to the conditions in the SQL statement,
it provides access to the data in these rows through a set of Get methods
The Resultset.next method is used to move to the next line in the ResultSet so that the next line becomes the current row.

Java Database programming