4, realize database lasting
(23) Establish a database table
(Translator: The original text is HSQL, here using mysql, version number is 4.0.20a)
Below is a SQL script that creates a database table product, use the database being Test:
/ *! 40014 set @old_unique_checks = @@ unique_checks, unique_checks = 0 * /;
/ *! 40014 set @OLD_FOREIGN_KEY_CHECKS = @@ foreign_key_checks, foreign_key_checks = 0 * /;
/ *! 40101 set @old_sql_mode = @@ sql_mode, sql_mode = no_auto_value_on_zero * /;
Create Database / *! 32312 if NOT EXSTS * / `TEST`;
Use `test`;
Create Table `Products (
`id` int (11) Not null default '0',
`Description` VARCHAR (255) Default '',
`price` Decimal (15, 2) Default '0.00',
Primary key (`ID`),
Key `Products_Description` (` Description`
) TYPE = MyISAM;
INSERT INTO `Products` (` ID`, `description`,` price`) Values (1, 'Lamp', '5.78'), (2, 'Table', '75.29), (3,' Chair ', '22.81 ');
/ *! 40101 set sql_mode = @ old_sql_mode * /;
/ *! 40014 SET FOREIGN_KEY_CHECKS = @ Old_Foreign_Key_Checks * /
/ *! 40014 set unique_checks = @ old_unique_checks */ /;
Different database SQL statements will be different, please make appropriate adjustments
(24) Creating a DAO for achieving JDBC
l Firstly, DAO Interface ProductManagerdao, provides a list of list products and growth product prices.
Package DB;
Import bus.product;
Import java.util.list;
Public interface productManagerdao {
Public List getProductList ();
Public Void Increaseprice (Product Prod, INT PCT);
}
l ProductManagerDaoJDBC Class implementation ProductManagerDao interface
Package DB;
Import bus.product;
Import java.util.list;
Import java.sql.resultset;
Import java.sql.sqlexception;
Import java.sql.types;
Import javax.sql.datasource; import org.apache.commons.logging.log;
Import org.apache.commons.logging.logfactory;
Import org.springframework.jdbc.object.mappingsqlquery;
Import Org.springFramework.jdbc.Object.sqlupdate;
Import org.springframework.jdbc.core.sqlparameter;
Public Class ProductManagerdaojdbc Implements ProductManagerdao {
/ ** Logger for this class and subclasses * /
Protected final log logger = logfactory.getlog (getclass ());
PRIVATE DATASOURCE DS;
Public List getProductList () {
Logger.info ("Getting Products!");
ProductQuery PQ = New ProductQuery (DS);
Return pq.execute ();
}
Public Void Increaseprice (Product Prod, Int PCT) {
Logger.info ("increasing price by" "%");
SQLUPDATE SU =
New SQLUPDATE (DS, "Update Products Set Price = Price * (100 ?) / 100 WHERE ID =?");
Su.DeclareParameter (New Sqlparameter ("Increase", Types.integer);
Su.DeclareParameter (New Sqlparameter ("ID", Types.integer);
Su.compile ();
Object [] OA = new object [2];
OA [0] = New Integer (PCT);
OA [1] = new integer (prodioget ());
INT count = su.update (oa);
Logger.info ("Rows Affected:" Count);
}
Public void setDataSource (DataSource DS) {
THIS.DS = DS;
}
Class ProductQuery Extends MappingsqlQuery {
ProductQuery (DataSource DS) {
Super (DS, "SELECT ID, Description, Price from Products");
COMPILE ();
}
Protected Object MapRow (ResultSet RS, INT Rownum) throws sqlexception {
Product prod = new product ();
Prod.setId (Rs.Getint ("ID"));
Prod.setdescription (rs.getstring ("description");
PROD.SETPRICE (NEW DOUBLE ("Price")))));
Return prod;
}
}
}
l Use the Spring JDBC framework. It is the advantage that there is no need to worry about the opening and closing of the connection and statement. No capture exceptions (unless you want to do this) L ProductQuery is an inner class, expands the mappingsqlquery class, package SQL query; DataSource in the construction method (defined in the bean configuration) and query the SQL statement to the parent class; by implementing the maprow () method, you can map each row of query results to the representative entity (usually javabean)
l The GetProductList () method is to create a query object productQuery, return to the query result by calling its execute () method: mapping a list of good Product objects
l In the InCreasePrice () method, use SQLUPDate to create an update statement with parameters (you need to pass DataSource); here you need to call the DeclareParameter () method to define the parameter type, and pass the parameter value in the form of an object array to execute updates. Statement, and return the number of rows affected
l Product classes need to increase the ID attribute to save the primary key value
Package bus;
Import java.io.serializable;
Public Class Product Implements Serializable {
Private int ID;
Private string description;
PRIVATE DOUBLE PRICE;
Public void setid (INT i) {
ID = i;
}
Public int getId () {
Return ID;
}
Public void setdescription (String s) {
Description = S;
}
Public string getdescription () {
Return description;
}
Public void setprice (double d) {
Price = d;
}
Public double getprice () {
Return Price;
}
}
