JDBC database connection
The necessity of close() :
If Java IO is not released, if there is an infinite loop, file resources have been occupied, the file can not be opened or deleted.
JDBC uses the network to connect to the database, and if it is not released, it can easily cause IO congestion, and the network can become congested and slow to perform other connections.
Why is there no implementation class for the interface that accesses the mysql database directly in the JDK API?
Because the market is not only mysql, there are other databases such as Oracle, Java does not know the internal structure of these databases, can not directly operate on the database. So it only provides the interface and the driver management class, which requires the database vendor to provide their own database driver, which is essentially a package of bytecode files for all the implementation classes of the interface (only.class but not.java).
The DriverManager class provided by the JDK loads the driver in, allowing us to use the related classes to manipulate the database. At the same time, because we are connected to the database through the network, so we need to get a database connection before the formal operation.
Did you use the network to connect to the database? What protocol are we using to use this network?
The TCP protocol means that mysql implements a function similar to ServerSocket. As a server, when we turn off the MySQL57 service in the system service, the server will be shut down, and the connection will fail.
Implementation steps
(Add the database driver mysql-connector-java-5.1.46.jar first if you don’t have a driver in your project)
Development Steps:
1 Register the driver
1 / / method
DriverManager.registerManager(new Driver());
// This takes up memory. The Driver is registered twice and executed once by a static block of code in the Driver class provided by the database vendor.
// We can use class reflection to optimize. As follows:
// Method 2 (recommended)
Class.forName("com.mysql.jdbc.Driver");
// Jdk1.6 is not required to write, JDK automatic registration.
// However, it is recommended to remember that connection pooling must be written in the future.
Copy the codeThe Driver class in parentheses here is the implementation class of the Driver interface provided by mysql-connector-java-5.1.46.jar
2 Obtain the database Connection object (java.sql.connection)
GetConnection (String URL,String User,String Password)
Parameters:
Url: Network address for connecting to the database
Connection protocol: Database vendor name :// Database IP address: database port number/name of the connected database. Such as:
mysql:localhost://3306/mydb
User: indicates the user name
Password: password
The static getConnection() method returns an implementation-class object of the Connection interface (the Connection object for the database).
String url = "mysql:localhost://3306/mydb";
String username = "root";
String password = "123456";
Connection conn = Driver.getConnection(url,username,password);
Copy the code3 Execution object of the SQL statement
Execute the SQL object :java.sql.Statement
Obtain the value using the connection object conn
Method: the createStatement() method gets the execution object of the SQL Statement. Statement createStatement() returns a value that returns the Statement object.
Statement stat = conn.createStatement();
Copy the code4 (This step if you want to query) The Statement object executes the SQL Statement
If the number of rows affected by the operation is greater than 0, the operation succeeds. If the number of rows affected by the operation is greater than 0, the operation succeeds.
The return value is the xx value on the xx row that was prompted after an operation was performed in the database.
String sql = "Insert into product values(value)";
int row = stat.executeUpdate(sql);
Copy the codeAfter inserting the database here, the database and Java code may be different, causing this line to be garbled
You are advised to change the URL to
mysql:localhost://3306/mydb?? CharacterEncoding = Coding in the library
5 Obtain the result set object of the database query.
ResultSet: java.sql.ResultSet
// Execute the query
String sql = "select * from product";
ResultSet result = stat.executeQuery(sql);
// You can see that the query and update (insert) methods are different
// Check whether result is null before iterating over result. Otherwise, it is possible to throw an empty exception.
// The result has a type next() of the iterator hasNext method, and we can use its next() to iterate.
// Fetch the result set :rs object method getXXX(String column name) This XXX is the data type of the column
/ / String varchar
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);
// When we have the Javabeans for the table, we can put them directly into the object properties, and then put that object into the collection
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);
}
// Check whether the set is null by iterating over the object toString. Check whether the set is null by iterating over the object toString.
if(! list.isEmpty){for(Product p : list){ System.out.println(p); }}Copy the codeAs you iterate through the result set, you can see that some columns are null, but getInt returns a value of 0.
If it’s getObject it will return a consistent null, but try not to use it. It’s irresponsible, it doesn’t match its data type.
6 Releasing Resources
result.close();
stat.close();
conn.close();
Copy the codeCustom JavaBean requirements
Must have: private member variables, get/set methods, no-parameter constructors, implementation of a serialized interface.
Extract duplicate code and load it into a custom utility class
By doing so, you can reduce the redundancy of your code.
1 can load the driver, read the configuration file into static code block (because only execute once)
2 Get the Connection and put it in a static method that returns Connection (because it returns a value)
3. Close the resource, put it in the closed static method, check whether the three resources are null, try.. Catch exceptions.
Note:
Declare the four attributes in a member variable, because the static code will assign a value to it, but the static code does not return a value, and the local variable will not be passed, and the fetching connection 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 do you keep configuration files separate?
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 to.class in.out, not just.java, but whenever you put it in your SRC project, it will end up in.out. This has the advantage of not putting the source code into the project when it goes live. The configuration file and.class are all in the same place, avoiding the hassle of moving files around.
How configuration files are read:
The dbconfig.properties configuration information is read to obtain the four database connection information, the connection information is not changed, read once, we can use static code block to achieve.
ClassLoader: An object that loads classes into memory and creates a bytecode file.
Ideas:
We can use the stream returned by getResourceAsStream of the classloader to load the configuration file. We use this stream instead of the newly created I/O stream because the new I/O stream needs to be manually closed, and the absolute path of the file is not easy to write. The stream lifecycle of the classloader is related to the classloader. When the classloader finishes loading, the classloader is useless and the stream is closed. Of course, we can manually close it early.
Application ClassLoader: Loads custom classes, such as jar classes
BootStrap ClassLoader: Loads the core class libraries of the JDK, such as Strings, Objects, collections, and IO
Extension ClassLoader :(less used) loads the Extension class library
The ClassLoader method: InputStream getResourceAsStream(String filename) returns a byte InputStream.
GetResourceAsStream () automatically scans files from the root directory of the source code, 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.class.getClassLoader ().getResourceasStream () {InputStream input = custom utility class.class.getClassLoader ().getResourceasStream ()"dbconfig.properties");
// Define the set
Properties prop = new Properties();
// The stream object reads the data from the configuration file and stores it in the collection
prop.load(input);
// Fetch data from the set
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
}
// Register the driver
class.forName(driver);
// There is no return value, so write to get the connection is invalid. You need to write them in a method, and since the method uses these properties, and the method is in the same class as the static block, you can declare them as static member variables, and then the static block calls them and assigns them.
}catch(Exception e){}
Copy the code