目录
列:mybatis项目练习:使用mapper动态代理方式实现各种查询方式
模糊查询、数据条数查询、if与where查询、array、list、包装类查询、一对一查询、一对多查询
列:使用jdbc连接数据库实现查询数据库
导入包
sql语句:创建订单表和用户表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `orders`订单表
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` INT(11) NOT NULL COMMENT '下单用户id',
`number` VARCHAR(32) NOT NULL COMMENT '订单号',
`createtime` DATETIME NOT NULL COMMENT '创建订单时间',
`note` VARCHAR(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders 插入数据
-- ----------------------------
INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', NULL);
INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', NULL);
INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', NULL);
-- ----------------------------
-- Table structure for `user` 用户表
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(32) NOT NULL COMMENT '用户名称',
`birthday` DATE DEFAULT NULL COMMENT '生日',
`sex` CHAR(1) DEFAULT NULL COMMENT '性别',
`address` VARCHAR(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', NULL, '2', NULL);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', NULL, NULL, NULL);
jdbc方式代码实现
写代码时导入jdbc的相关包
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcDemo {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 通过驱动管理类获取数据库链接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "123");
// 定义sql语句 ?表示占位符
String sql = "select * from user where username = ?";
// 获取预处理statement
preparedStatement = connection.prepareStatement(sql);
// 设置参数,第一个参数为sql语句中参数的序号(从1开始),第二个参数为设置的参数值
preparedStatement.setString(1, "王五");
// 向数据库发出sql执行查询,查询出结果集
resultSet = preparedStatement.executeQuery();
// 遍历查询结果集
while (resultSet.next()) {
System.out.println(resultSet.getString("id") + " " + resultSet.getString("username"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}