mysql 连接2表数据_数据库mysql笔记2多表及常用数据库连接池的使用

MySQL笔记2

1.如何创建带有外键的表

两种方式，一是创建表时，就在字段后面添加外键约束的语法声明

如：

主表：

create table A(

A_id...

...

);

从表：

create table B(

字段1 类型 primary key [auto increament],

字段2 类型,

B_id 类型，

constraint [约束名] foreign key 从表B(B_id) references

主表A(A_id)

);

二是，在创建好的表之后，在额外用语句添加外键约束

如：

alert table B add [constraint] [外键名_fk] foreign key 从表B(B_id)

references 主表A(A_id);

2.表的主键及从表的外键如何删除

主键删除：(前提是主键没有自增长约束(即没有auto increament))

alter table 表名 drop primary key;

从表外键删除：

alter table 从表B drop foreign key 外键名_fk;

3.三个关联查询

3.1多表的关系：一对一，一对多，多对多

注意：

1.从表无法添加或更新 主表中不存在的数据

2.主表无法删除或更新 从表中已经使用的数据

3.2如何查询：

1.交叉查询--得笛卡尔积图表--一般不用

select * from A,B [条件]

2.内连接查询--可以跟自己表连接

隐式：select * from A,B where 条件

显示：select * reom A inner join B on 条件

3.外连接查询

左连接：select * from A left join B on 条件

右连接：select * from A right join B on 条件

3.3子查询--就是嵌套--查询的结果作为条件或表再使用

1.单行子查询，也就是先查询的结果是一个值，可以将其作为比较条件，再次参与查询

2.多行子查询，第一次查询的结果相当于一个数组，可以将其作为比如in条件，再次查询

3.多行多列子查询，第一次查询的结果相当于一个表，将其作为表，再参与查询，往往需要as一个别名

4.三种常用常见的数据库连接池的使用规则及方法

4.1Apache的dbcp/pool提供的jar包，并创建键值名规范的xx.properties配置文件，结合mysql-connector的jar包

4.2c3p0提供的c3p0的jar包，结合规范格式的c3p0-config.xml配置文件

4.3Apache的dbutils工具类，将数据库查询结果集封装成9种常见格式，重要的两个东西就是QueryRunner和ResultSetHandler(对ReSultset封装成了9个常用结果集)，但是事前必须先构造一个属性名与数据库字段名相同的类，用来接收查询结果集，这里主要区别上面两个，这个是用他生成的数据池，然后提供给QueryRunner使用，而不是用connection

5.针对4的三个非常有用和常用的例子，可以直接使用

5.1

package com.dou;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPFactoryDemo {

public static void main(String[] args) throws Exception

{

// TODO Auto-generated method stub

//3.加载属性文件，并创建数据池对象

FileInputStream fins = new

FileInputStream("dbcp.properties");

Properties prop = new Properties();

prop.load(fins);

DataSource dataSource =

BasicDataSourceFactory.createDataSource(prop);

Connection con = dataSource.getConnection();

String sql = "select * from emp where deptno=?";

PreparedStatement pst = con.prepareStatement(sql);

try {

pst.setInt(1, 10);//【索引从1开始】

ResultSet res = pst.executeQuery();

while(res.next()){

//【索引从1开始getXxx(1,2,3..)】或者getXxx("字段名")

System.out.println(res.getObject(1)+"-"+res.getObject("ename")+"-"+res.getString(3)+"-"+res.getObject("mgr"));

}

con.close();

res.close();

fins.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

dbcp.properties:

5.2

package com.dou2.c3p0;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {

private static ComboPooledDataSource ds = new

ComboPooledDataSource();

public static Connection getConnection(){

Connection con = null;

try {

con = ds.getConnection();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return con;

}

//提供连接池的--Apache的dbutils会用到

public static DataSource

getDataSource(){//提供连接池的--Apache的dbutils会用到

return ds;

}

//提供链接的方法

public static void closeResource(Connection con,Statement

stat,ResultSet res){

if(con!=null){

try {

con.close();//将该连接还给数据池ds

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

con = null;

}

}

if(stat!=null){

try {

stat.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

stat = null;

}

}

if(res!=null){

try {

res.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

res = null;

}

}

}

}

c3p0-config.xml:

package com.dou2.c3p0;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class c3p0Demo {

public static void main(String[] args) {

// TODO Auto-generated method stub

Connection con = C3P0Utils.getConnection();

String sql = "select * from emp where deptno=?";

try {

PreparedStatement pst = con.prepareStatement(sql);

pst.setInt(1, 10);//【索引从1开始】

ResultSet res = pst.executeQuery();

while(res.next()){

//【索引从1开始getXxx(1,2,3..)】或者getXxx("字段名")

System.out.println(res.getObject(1)+"-"+res.getObject("ename")+"-"+res.getString(3)+"-"+res.getObject("mgr"));

}

con.close();

res.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

5.3

package com.dou.dbutils;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class ApacheDBUtils {

private static ComboPooledDataSource ds = new

ComboPooledDataSource();

public static Connection getConnection(){

Connection con = null;

try {

con = ds.getConnection();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return con;

}

//提供连接池的--Apache的dbutils会用到

public static DataSource

getDataSource(){//提供连接池的--Apache的dbutils会用到

return ds;

}

//提供链接的方法

public static void closeResource(Connection con,Statement

stat,ResultSet res){

if(con!=null){

try {

con.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

con = null;

}

}

if(stat!=null){

try {

stat.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

stat = null;

}

}

if(res!=null){

try {

res.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

res = null;

}

}

}

}

User:

package com.dou.dbutils;

public class User {

private int uid;

private String uname;

public User() {

super();

// TODO Auto-generated constructor stub

}

public User(int uid, String uname) {

super();

this.uid = uid;

this.uname = uname;

}

public int getUid() {

return uid;

}

public void setUid(int uid) {

this.uid = uid;

}

public String getUname() {

return uname;

}

public void setUname(String uname) {

this.uname = uname;

}

@Override

public String toString() {

return "[uid=" + uid + ", uname=" + uname + "]";

}

}

Demo:

package com.dou.dbutils;

import java.sql.SQLException;

import java.util.List;

import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanHandler;

import

org.apache.commons.dbutils.handlers.BeanListHandler;

import

org.apache.commons.dbutils.handlers.ColumnListHandler;

import org.apache.commons.dbutils.handlers.MapHandler;

import

org.apache.commons.dbutils.handlers.MapListHandler;

import

org.apache.commons.dbutils.handlers.ScalarHandler;

public class DBUtilsHandlerDemo {

public static void main(String[] args) {

// TODO Auto-generated method stub

DBUtilsHandlerDemo demo1 = new DBUtilsHandlerDemo();

System.out.println("-------One---------");

demo1.queryOne();

System.out.println("--------Many--------");

demo1.queryMany();

System.out.println("--------OneMap--------");

demo1.queryOneMap();

System.out.println("--------ManyMap--------");

demo1.queryManyMap();

System.out.println("--------Scalar--------");

demo1.queryScalar();

System.out.println("--------Columns--------");

demo1.queryColumns();

}

public void queryOne(){

QueryRunner qr = new

QueryRunner(ApacheDBUtils.getDataSource());

String sql = "select * from user where uid=?";

Object[] objs = {1};

try {

User user = qr.query(sql, new

BeanHandler(User.class),objs);

System.out.println(user.toString());

System.out.println(user.getUid()+"->"+user.getUname());

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void queryMany(){

QueryRunner qr = new

QueryRunner(ApacheDBUtils.getDataSource());

String sql = "select * from user";

try {

List users = qr.query(sql, new

BeanListHandler(User.class));

for (User user : users) {

System.out.println(user.toString());

System.out.println(user.getUid()+"->"+user.getUname());

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void queryOneMap(){

QueryRunner qr = new

QueryRunner(ApacheDBUtils.getDataSource());

String sql = "select * from user where uid=?";

Object[] objs = {1};

try {

Map one = qr.query(sql, new MapHandler(),objs);

System.out.println(one.toString());

System.out.println(one.get("uid")+"->"+one.get("uname"));

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void queryManyMap(){

QueryRunner qr = new

QueryRunner(ApacheDBUtils.getDataSource());

String sql = "select * from user";

try {

List> list = qr.query(sql, new

MapListHandler());

for (Map user : list) {

System.out.println(user.toString());

System.out.println(user.get("uid")+"->"+user.get("uname"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void queryScalar(){

QueryRunner qr = new

QueryRunner(ApacheDBUtils.getDataSource());

String sql = "select count(*) from user";

try {

Object num = qr.query(sql, new ScalarHandler());

System.out.println("查询到了数据条数："+num);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void queryColumns(){

QueryRunner qr = new

QueryRunner(ApacheDBUtils.getDataSource());

String sql = "select uname from user";

try {

Listlist = qr.query(sql, new

ColumnListHandler("uname"));

for (Object obj : list) {

System.out.println(obj.toString());

System.out.println(obj);

System.out.println("--");

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}