JDBC Database Connection

The necessity of close() :

For example, if Java IO is not released, if there is an infinite loop, the file resources are always occupied, the file cannot be opened or deleted.

JDBC uses the network to connect to the database, and if it is not released, it is easy for IO to be tied up. The network can become crowded and congested, and other connections can become inefficient.

Why isn’t there an implementation class in the JDK API that directly accesses the mysql database?

Java does not know the internal structure of these databases and cannot operate them directly. So only the interface and driver management classes are provided, and it requires those database vendors to provide their own database drivers, which are actually compressed packages of all the interface implementation classes (only.class but not.java).

The JDK provides a DriverManager class to load the driver so that we can use the related classes to manipulate the database. Also, since we connect to the database through the network, we need to obtain a database connection before we can proceed.

Using a network to connect to the database? What protocol do we use to use this network?

TCP protocol, that is, mysql implements a function similar to ServerSocket. As a server, when we turn off MySQL57 service in the system service, the server is closed, and this time the connection must fail.

Implementation steps

Mysql-connector-java-5.1.46. jar = mysql-connector-java-5.1.46.jar = mysql-connector-java-5.1.46.jar = mysql-connector-java-5.1.46.jar = mysql-connector-java-5.1.46.jar

Development steps:

1 Register the driver

/ / method 1 DriverManager. RegisterManager (new Driver ()); // This takes up memory, and the Driver is registered twice, once by a static block of code in the Driver class provided by the database vendor. // We can use class reflection to optimize. // Method 2 (recommended) class.forname (" com.mysql.jdbc.driver "); //jdk1.6 can be automatically registered without writing. // However, it is recommended to remember that connection pooling will be written in the future.Copy the code

The Driver class in parentheses here is the implementation class of the Driver interface provided by mysql-connector-java-5.1.46.jar

2 Obtaining the database Connection object (java.sql.connection)

Static method getConnection(String URL,String user,String Password) used by DriverManager

Parameters:

Url: network address for connecting to the database

Connection protocol: Database vendor name :// DATABASE IP address: database port number/database name. Such as:

mysql:localhost://3306/mydb

User: indicates the user name

Password: password

The static getConnection() method returns the implementation object of the Connection interface (the database Connection object).

String url = "mysql:localhost://3306/mydb";
String username = "root";
String password = "123456";
Connection conn = Driver.getConnection(url,username,password);
Copy the code

3 The execution object of the SQL statement

Execute the SQL object: java.sql.statement

Obtained by connecting to the conn object

CreateStatement() obtains the Statement object. Statement createStatement() returns the Statement object.

Statement stat = conn.createStatement();
Copy the code

The Statement object executes an SQL Statement

The stat object method executeUpdate(String SQL), which returns an int, succeeds if the number of rows affected by the operation is greater than 0.

The return value is the value of xx that affects xx rows after the operation in the database is prompted.

String SQL = "insert into product values "; int row = stat.executeUpdate(sql);Copy the code

When the database is inserted here, the encoding of the database and Java may be different, resulting in garbled lines

You are advised to change the URL to

mysql:localhost://3306/mydb?? CharacterEncoding = Encoding in the library

5 Obtain the result set object of the database query,

ResultSet: java.sql.ResultSet

String SQL = "select * from product"; ResultSet result = stat.executeQuery(sql); // Check if the result is null before iterating through it. Otherwise, it is possible to throw an empty exception. // This result has a type next() of the iterator hasnext method, The rs object method getXXX(String column name) is the data type of this column. // VARCHar corresponds to String List<Product> List = new ArrayList<Product>(); while(result.next()){ int pid = rs.getInt("pid"); String pname = rs.getString("pname"); double price = rs.getDouble("price"); int num = rs.getInt("num"); int cno = rs.getInt("cno"); System.out.println(pid +","+pname+","+price+","+num+","+cno); Product p = new Prodect(rs.getint ("pid")); p.setId(rs.getString("pname")); p.setPname(rs.getString("pname")); p.setPrice(rs.getDouble("price")); p.setNum(rs.getInt("num")); p.setCno(rs.getInt("cno")); list.add(p); } // toString = toString = toString = toString = toString = toString; if(! list.isEmpty){ for(Product p : list){ System.out.println(p); }}Copy the code

As you iterate through the result set, you can see that some columns are null, but getInt returns a value of 0.

GetObject returns a consistent null, but try not to use it, it’s irresponsible, it doesn’t fit its own data type.

6 Releasing Resources

result.close();
stat.close();
conn.close();
Copy the code

Custom JavaBean requirements

Mandatory: privatizing member variables, get/set methods, no-argument constructors, implementing serialization interfaces.

Extract duplicate code into custom utility classes

By doing so, you reduce redundancy in your code.

1 You can put the load driver, read configuration file into a static code block (because it is executed only once)

2 Get the Connection and put it in a static method that returns Connection (because it returns)

3 close the resource and place it in the closed static method. Check whether the three resources are null. Catch exceptions.

Note:

To declare the four attributes in the member variable, because the static code will be assigned to it after fetching, but the static code does not return a value, local variables will not be passed, and fetch connections will use it. Try not to use the final modification, because if the final once declared to its initial value (compile time), and also can’t literally to the initial value, because once happened to the static block of code is unusual, to find a resource file or found no attribute, the program will be carried out in accordance with the value of the initialization down not to stop. You can declare it as a private static String.

Location of the configuration file

Why separate configuration files?

If directly to the username, password, url, driver information directly writes code, it is very trouble to modify, put it in. The properties file, it is very clear, still have even if, improved the security of the code, you can just give the user modify the configuration file, You don’t have to change the code directly.

If you put it in the SRC directory of your project, the Java file will compile and generate.class in the.out directory, not just.java, if you’re in a SRC project, you’ll end up in.out. One advantage of this is that the source code is not put into the project when it goes live, and the configuration file and the.class are all in one place, avoiding the trouble of moving files.

Principles of reading configuration files:

Properties to obtain the four major database connection information, the connection information does not change, read once, we can use static code block to achieve.

ClassLoader: An object that loads classes into memory and creates bytecode files.

Ideas:

We can use the stream returned by the class loader’s getResourceAsStream method to load the configuration file. We use this stream instead of the newly created IO stream because the new IO stream needs to be closed manually, the IO is passed an absolute path, and the absolute path of the file is not conveniently written. The stream life cycle of the class loader is related to the class loader. After the class loader is loaded, the class loader is no longer useful and the stream is closed. Of course, we can manually close it early.

Application ClassLoader: Loads custom classes, jar classes

BootStrap ClassLoader: Loads the core JDK libraries, including String, Object, collection, and IO

Extension ClassLoader: loads the Extension class library

ClassLoader method: InputStream getResourceAsStream(String filename) Returns a stream of byte input.

GetResourceAsStream () will automatically scan the files from the source code’s root directory, the SRC directory.

Read the Configuration file (Four properties)

private static String driver; private static String url; private static String username; private static String password; Static {try{InputStream input = custom utility class.classLoader ().getResourceasStream ("dbconfig.properties"); // define set Properties prop = new Properties(); // The stream object reads the data from the configuration file and stores it in the collection prop.load(input); // Get data from collection driver = prop.getProperty("driver"); url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); } // Register the driver class.forname (driver); // No value can be returned here, so writing to get a connection is invalid. They need to be in the method, and since the method uses these attributes and the method is in the same class as the static code block, you can declare them as static member variables and assign them to the static code block. }catch(Exception e){}Copy the code