An example of a multiple-table query with "Javaweb: multiple Tables" __web

1, First there are two tables, a OrderItem table (from the table), an Product table (primary table) as shown in the following figure

OrderItem table:

Product table:

2, if you want to query the two tables of the specified column information, such as OrderItem table in the Count,subtotal,product table pname,pimage, etc.

Then it involves multiple table queries, and the result of the query is encapsulated with:map<string, object>

and the SQL language should pay attention to his writing format: Please see below, I query the corresponding OrderItem table in the OID is a value of the information, this OID is the third table's primary key

Select O.count,o.subtotal,p.pimage,p.pname,p.shop_price from  orderitem o,product p where o.pid=p.pid and o.oid=?

As you can see, O and P give me a simplified name for the table,o.pid=p.pid This is the key to representing their master-slave table correspondence or the basic requirements of the query, followed by

O.oid is the requirement that you want to set on the basis of the previous master-slave table relationship.

The complete DAO Layer query code is as follows:

Public list<map<string,object>> Findorderneedinfo (String oid) throws SQLException {
		//Using multiple table queries, Get the information you want
		queryrunner qr=new Queryrunner (Datasourceutils.getdatasource ());
		String sql= "Select O.count,o.subtotal,p.pimage,p.pname,p.shop_price from  orderitem o,product p where o.pid=p.pid and o.oid=? ";
		list<map<string, object>> query = qr.query (sql, New Maplisthandler (), OID);
		Each row of the query result constitutes a map collection, and the key of the map is the query's field name (that is, the column name) return to query
		.
	

3, Dbutils to the data package in the database, often the following, we will often use, need to memorize.

the principle of encapsulation : According to the column name and the variable name in the entity, the data in the database is marshaled into the entity, and the inconsistency is not sealed.

Another tool class we often use: Beanutils.populate (bean,properties) is the same principle.

4, three table query examples