MySQL多表&JDBC
来源:互联网 发布:身份证sql判断 编辑:程序博客网 时间:2024/05/16 12:47
1、外键
主表:主键。从表:外键。
从表外键类型,必须与主表主键类型一致。
从表外键的值是对主表主键的引用。
注意:
从表外键不能添加主表中不存在的记录;
主表不能删除从表中已经引用的记录。
数据准备:
create table category( cid varchar(32) primary key, cname varchar(100));create table product( pid varchar(32) primary key, pname varchar(40), price double, category_id varchar(32));insert into category(cid,cname) values('c001','家电');insert into category(cid,cname) values('c002','服饰');insert into category(cid,cname) values('c003','化妆品');insert into product(pid,pname,price,category_id) values('p001','联想','5000','c001');insert into product(pid,pname,price,category_id) values('p002','海尔','5000','c001');insert into product(pid,pname,price,category_id) values('p003','雷神','5000','c001');insert into product(pid,pname,price,category_id) values('p004','JACK JONES','800','c002');insert into product(pid,pname,price,category_id) values('p005','真维斯','200','c002');insert into product(pid,pname,price,category_id) values('p006','花花公子','440','c002');insert into product(pid,pname,price,category_id) values('p007','劲霸','2000','c002');insert into product(pid,pname,price,category_id) values('p008','香奈儿','800','c003');insert into product(pid,pname,price,category_id) values('p009','相宜本草','200','c003');
添加约束:
alter table product add foreign key(category_id) references category(cid);
2、表和表的关系
一对多:主表和从表;
多对多:两个主表和一个从表。
//订单表create table orders( oid varchar(32) primary key, totalprice double);//订单项表create table orderitem( oid varchar(50), pid varchar(50));//联合主键alter table orderitem add primary key(oid,pid);//订单表和订单项表的主外键关系alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);//订单表和product表的主外键关系alter table orderitem add constraint orderitem1_product_fk foreign key(pid) references product(pid);
3、查询
3.1、多表查询
内连接:
SELECT * FROM category INNER JOIN product ON cid=category_id;
或者:
SELECT * FROM category c,product p WHERE c.cid = p.category_id;
外连接:
SELECT * FROM category LEFT JOIN product ON cid=category_id;SELECT * FROM category RIGHT JOIN product ON cid=category_id;
左外连接:左表全部及两个表的交集;
内连接:查询两个表交集;
右外连接:右表全部及两个表的交集。
3.2、子查询
一条select语句结果作为另一条select语法一部分。
SELECT * FROM product WHERE category_id = (SELECT cid FROM category WHERE cname='化妆品');
4、工具类
工具类一
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class JDBCUtils_V1 { /** * 获取连接方法 * @return */ public static Connection getConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "root", "******"); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }}
工具类一的使用:
/** * 根据id查询用户信息 */@Testpublic void testFindUserById() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { // 1.获取连接 conn = JDBCUtils_V1.getConnection(); // 2.编写sql语句 String sql = "select * from tbl_user where uid=?"; // 3.获取执行sql语句对象 pstmt = conn.prepareStatement(sql); // 4.设置参数 pstmt.setInt(1, 2); // 5.执行查询操作 rs = pstmt.executeQuery(); // 6.处理结果集 while (rs.next()) { System.out.println(rs.getString(2) + "----" + rs.getString("upassword")); } // 释放资源放在此处行么?【不行滴!】 } catch (SQLException e) { e.printStackTrace(); } finally { // 7.释放资源 JDBCUtils_V1.release(conn, pstmt, rs); }}
工具类二
新建xx.properties文件:
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/web?useUnicode=true&characterEncoding=utf8username=rootpassword=****
properties文件建议放在src下,一行一组数据,格式是key=value,value不支持中文。不要加空格。
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ResourceBundle;public class JDBCUtils_V2 { private static String driver; private static String url; private static String username; private static String password; /** * 静态代码块加载配置文件信息 */ static{ ResourceBundle bundle = ResourceBundle.getBundle("xx"); driver = bundle.getString("driver"); url = bundle.getString("url"); username = bundle.getString("username"); password = bundle.getString("password"); } /** * 获取连接方法 * * @return */ public static Connection getConnection() { Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }}
使用:
/** * 添加用户信息方法 */@Testpublic void testAdd() { Connection conn = null; PreparedStatement pstmt = null; try { // 1.获取连接 conn = JDBCUtils_V2.getConnection(); // 2.编写sql语句 String sql = "insert into tbl_user values(null,?,?)"; // 3.获取执行sql语句对象 pstmt = conn.prepareStatement(sql); // 4.设置参数 pstmt.setString(1, "lisi"); pstmt.setString(2, "hehe"); // 5.执行插入操作 int row = pstmt.executeUpdate(); if (row > 0) { System.out.println("添加成功!"); } else { System.out.println("添加失败!"); } } catch (Exception e) { throw new RuntimeException(e); } finally { // 6.释放资源 JDBCUtils_V2.release(conn, pstmt, null); }}
工具类三
进一步对properties文件处理
import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import java.util.ResourceBundle;public class JDBCUtils_V3 { private static String driver; private static String url; private static String username; private static String password; /** * 静态代码块加载配置文件信息 */ static { try { // 1.通过当前类获取类加载器 ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader(); // 2.通过类加载器的方法获得一个输入流 InputStream is = classLoader.getResourceAsStream("xx.properties"); // 3.创建一个properties对象 Properties props = new Properties(); // 4.加载输入流 props.load(is); // 5.获取相关参数的值 driver = props.getProperty("driver"); url = props.getProperty("url"); username = props.getProperty("username"); password = props.getProperty("password"); } catch (IOException e) { e.printStackTrace(); } } /** * 获取连接方法 * * @return */ public static Connection getConnection() { Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }}
使用
/** * 根据id更新用户信息方法 */@Testpublic void testUpdateById() { Connection conn = null; PreparedStatement pstmt = null; try { // 1.获取连接 conn = JDBCUtils_V3.getConnection(); // 2.编写sql语句 String sql = "update tbl_user set upassword=? where uid=?"; // 3.获取执行sql语句对象 pstmt = conn.prepareStatement(sql); // 4.设置参数 pstmt.setString(1, "999"); pstmt.setInt(2, 3); // 5.执行更新操作 int row = pstmt.executeUpdate(); if (row > 0) { System.out.println("更新成功!"); } else { System.out.println("更新失败!"); } } catch (Exception e) { throw new RuntimeException(e); } finally { // 6.释放资源 JDBCUtils_V3.release(conn, pstmt, null); }}/** * 根据id删除信息方法 */@Testpublic void testDeleteById() { Connection conn = null; PreparedStatement pstmt = null; try { // 1.获取连接 conn = JDBCUtils_V3.getConnection(); // 2.编写sql语句 String sql = "delete from tbl_user where uid=?"; // 3.获取执行sql语句对象 pstmt = conn.prepareStatement(sql); // 4.设置参数 pstmt.setInt(1, 4); // 5.执行删除操作 int row = pstmt.executeUpdate(); if (row > 0) { System.out.println("删除成功!"); } else { System.out.println("删除失败!"); } } catch (Exception e) { throw new RuntimeException(e); } finally { // 6.释放资源 JDBCUtils_V3.release(conn, pstmt, null); }}
阅读全文
0 0
- Mysql多表&JDBC
- MySQL多表&JDBC
- jdbc---mysql
- jdbc mysql
- jdbc-mysql
- MySQL - JDBC
- jdbc mysql
- Mysql+JDBC
- JDBC MySql
- JDBC-Mysql
- JDBC-mysql
- JDBC MYSQL
- mysql JDBC
- Jdbc-mysql
- JDBC,MySQL
- mysql jdbc
- jdbc+mysql
- JDBC(mysql)
- 9. Palindrome Number
- python机器学习-数据规模统计
- 利用WHID为隔离主机建立隐秘通道
- POJ总结(3299)
- OpenCV遍历图片像素点
- MySQL多表&JDBC
- 压缩下载文件
- 【学习笔记】面试集锦【3】
- mq小程序二
- pixhawk当中关于NMEA类型的gps数据处理流程
- python机器学习-数据类型统计
- Linux常用命令学习
- HDOJ HDU 1029 Ignatius and the Princess IV
- AJAX与json php进行查询与新建