September 21 training diary

Evaluate the code of a student using preparestatement (not to publish his/her name). The program code is as follows:
Package CN. incast;

Import java. Io. ioexception;
Import java. SQL. connection;
Import java. SQL. preparedstatement;
Import java. SQL. resultset;
Import java. SQL. sqlexception;
Import java. util. Logging. Logger;

Public class democlass {
Private connectionconfig = NULL;
// Private logger log = logger. getlogger ("cn. incast ");
Private connection Cn = NULL;
Private preparedstatement pstmt = NULL;
Private resultset rs = NULL;
Public Boolean doquery (string name, string pass) throws ioexception, classnotfoundexception, sqlexception
{
CN = This. connectionconfig. foundconnection ();
Pstmt = cn. preparestatement ("select ID from student where user =? And pass =? ");
// If the statement class is used, the query statement should be "select ID from student where name = '" + name + "'pass ='" + pass "'"
// It is easy to appear
// Use SQL Server because ''in SQL Server is an escape character
// Name = Name. Replace ("'","''");
// Use MySQL because/is an escape character in MySQL and/is a character in Java/
Name = Name. Replace ("'","//'");
Pstmt. setstring (1, name );
Pstmt. setstring (2, pass );
Rs = pstmt.exe cutequery ();

Boolean bool = true;
If (Rs. Next ())
{
// Log.info ("Welcome come" + name + "! ");
This. doclose ();
Return bool;
}
Else
{
// Log.info ("the username and userpassword error! ");
Bool = false;
This. doclose ();
Return bool;
}

}

Public void doclose ()
{
Connectionconfig. Close (RS, pstmt, CN );
}
Public connectionconfig getconnectionconfig (){
Return connectionconfig;
}

Public void setconnectionconfig (connectionconfig ){
This. connectionconfig = connectionconfig;
}
 

}
Problem:
(1) After preparestatement is used, the single quotation mark (') in the parameter does not need to be escaped. The name of the student is name. replace ("'", "//'"); the statement is obviously faulty and self-defeating!
(2) For Boolean bool = true; this statement: it is better to name the variable bool than bfound. In addition, setting the initial value of a Boolean variable to false is much better than setting it to true!
(3) If... else... the code block is too naive. If and the code in else have many duplicates, you should put these duplicates in if... else... after the code block is modified to the following form, it is much more elegant:
Boolean bfound = false;
If (Rs. Next ())
{
Bfound = true;
}

This. doclose ();
Return bfound;
Two tips:
(1) use MD5 encryption to store passwords in databases in actual projects
(2) If a function accepts many parameters, you should package these parameters into an object.
Thoughts:
Write the MySQL connection string as JDBC: mysql: // itcast: What do you mean?

Review and compile the MySQL stored procedure and explain how to call the stored procedure and obtain the returned values of the stored procedure in JDBC. Li Jie's experiment experience: If you get the Stored Procedure return value of SQL Server, you must use the registeroutparameter method to register the returned parameter. When we get the output parameter value in MySQL today, we did not call registeroutparameter.

Result of the experiment completed by Li Jie: Rs. Next can be used for the duration of network disconnection. However, for a long time, CN. createstatement won't work.
Cause and actual problems: the network is a virtual connection. When the Web server and database server are not on the same server, the network is disconnected or the database server is restarted, this is also CN. createstatement is one of the possible causes of failure.

The datasource implementation should be provided in various drivers. You can view the jar packages of the SQL Server and MySQL drivers and the datasource implementation classes they provide.
The implementation principle of the connection pool is explained, and the packaging design mode is introduced again. The Code is as follows:
Class pooledconnection implements connection
{
Connection realconnection = NULL;
Pool pool;
Pooledconnection (connection realconnection, pool)
{
This. realconnection = realconnection;
This. Pool = pool;
}

Createstatement ()
{
Realconnection. createstatement ();
}

Preparecall ()
{
Realconnection. preparecall ();
}
 
Close ()
{
// Realconnection. Close ();
Pool. Release (realconnection );
}
}

Class pool
{
Public connection getconnection ()
{
Connection realconnection = xxxx;
Return new pooledconnection (realconnection, this );
}
}

Data sources implemented by various open-source frameworks: Spring drivermanagerdatasource, Jakarta basicdatasource (and the corresponding data source factory basiccecefactory), ibatis simpledatasource (and the corresponding data source factory), and hibernate drivermanagerconnectionprovider. During the ibatis experiment, we found that even if the password is "", this part must be set. The cause of the error is to view the source code in eclipse.
The following is the configuration file for experiment on the above data sources using spring in the classroom field explanation:
<? XML version = "1.0" encoding = "UTF-8"?>
<! Doctype beans public "-// spring // DTD bean // en" "http://www.springframework.org/dtd/spring-beans.dtd">

<Beans>
<Bean id = "dsdemo" class = "cn. itcast. dsdemo">
<Property name = "datasource">
<Ref bean = "ds"> </Ref>
</Property>
</Bean>
<Bean id = "ds" class = "com. ibatis. Common. JDBC. simpledatasource">
<Constructor-Arg>
<Props>
<Prop key = "JDBC. Driver"> com. MySQL. JDBC. Driver </prop>
<Prop key = "JDBC. connectionurl"> JDBC: mysql: // itcast </prop>
<Prop key = "JDBC. username"> root </prop>
<Prop key = "JDBC. Password"> </prop>
</Props>
</Constructor-Arg>
</Bean>
<! -- Bean id = "ds" class = "org. springframework. JDBC. datasource. drivermanagerdatasource">
<Property name = "driverclassname">
<Value> com. MySQL. JDBC. Driver </value>
</Property>
<Property name = "url">
<Value> JDBC: mysql: // itcast </value>
</Property>
<Property name = "username">
<Value> root </value>
</Property>
</Bean -->
<! -- Bean id = "ds" class = "org. Apache. commons. DBCP. basicdatasource">
<Property name = "driverclassname">
<Value> com. MySQL. JDBC. Driver </value>
</Property>
<Property name = "url">
<Value> JDBC: mysql: // itcast </value>
</Property>
<Property name = "username">
<Value> root </value>
</Property>
</Bean -->
</Beans>
Job:
Group 1: DBCP experiment. Use basicdatasourcefactory to create the basicdatasource object in the program.
Group 2: hibernate experiment. Use the drivermanagerconnectionprovider of Hibernate to obtain the connection in the program.
Group 3: For the ibatis experiment, use hard encoding in the program to create a simpledatasource object using simpleperformancefactory.
Group 4: Li Jie performed an experiment to call the stored function and obtain the returned values of the stored function.