文章目录
javaBean
-具备如下的三个特点
- 私有属性
- 无参构造
- 为属性提供get,set方法
public class Item {
private int id;
private String title;
private int price;
private int num;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
@Override
public String toString() {
return "Item [id=" + id + ", title=" + title + ", price=" + price
+ ", num=" + num + "]";
}
}
// 插入数据,传入JavaBean对象作为参数
public void insert(Item item) {
Connection connection = null;
PreparedStatement statement = null; // 创建预处理对象
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "insert into t_item(id,title,price,num) values(?,?,?,?)";
statement = connection.prepareStatement(sql);
statement.setInt(1, item.getId());
statement.setString(2, item.getTitle()); // 为预处理对象中的占位符赋值
statement.setInt(3, item.getPrice());
statement.setInt(4, item.getNum());
int row = statement.executeUpdate();
System.out.println(row);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
// 查询数据,封装在JavaBean中
public List<Item> findAll() {
ArrayList<Item> items = new ArrayList<Item>();
Connection connection = null;
PreparedStatement statement = null; // 创建预处理对象
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "select * from t_item where price>100";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()) {
String title = resultSet.getString("title");
int price = resultSet.getInt("price");
int num = resultSet.getInt("num");
Item item = new Item();
item.setNum(num);
item.setPrice(price);
item.setTitle(title);
items.add(item);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
return items;
}
//更新操作,传入JavaBean对象,只需要将要更新的条件封装进去即可
public void update(Item item) {
Connection connection = null;
PreparedStatement statement = null; // 创建预处理对象
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "update t_item set title=?,num=?,price=? where id=?";
statement = connection.prepareStatement(sql);
//设置其中的占位符的值
statement.setString(1, item.getTitle());
statement.setInt(2, item.getNum());
statement.setInt(3, item.getPrice());
statement.setInt(4, item.getId());
int row = statement.executeUpdate();
System.out.println(row);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
// 删除的数据,其中参数传入的还是JavaBean对象
public void delete(Item item) {
Connection connection = null;
PreparedStatement statement = null; // 创建预处理对象
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "delete from t_item where id=?";
statement = connection.prepareStatement(sql);
statement.setInt(1, item.getId());
int row = statement.executeUpdate();
System.out.println(row);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
statement.addBatch(sql);
statement.executeBatch();
@Test
public void testStatement() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
statement = connection.createStatement(); // 创建Statement语句对象
String sql1 = "insert into t_item(id,title) values(100,'asaa')";
String sql2 = "insert into t_item(id,title) values(101,'asaa')";
String sql3 = "insert into t_item(id,title) values(102,'asaa')";
// 添加批量操作
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);
// 执行批量操作
statement.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
statement.addBatch()
statement.executeBatch();
@Test
public void testPreparedStatement() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "insert into t_item(id,title) values(?,?)";
statement = connection.prepareStatement(sql);
statement.setInt(1, 200);
statement.setString(2, "联想电脑");
statement.addBatch(); // 添加上面的数据到Batch中
statement.setInt(1, 201);
statement.setString(2, "华硕笔记本");
statement.addBatch(); // 添加上面的数据到Batch中
statement.setInt(1, 202);
statement.setString(2, "海尔洗衣机");
statement.addBatch(); // 添加上面的数据到Batch中
statement.executeBatch(); // 执行批量操作
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
clearBatch()
)
@Test
public void test1(){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "insert into t_v(name) values(?)";
statement=connection.prepareStatement(sql);
for(int i=0;i<100;i++){
statement.setString(1, "name"+(i+1));
statement.addBatch();
//为了避免内存溢出,当批量操作数量达到一定值时先执行一次,在向其中添加
//每二十次添加一次
if (i%20==0) {
statement.executeBatch(); //执行批量操作
statement.clearBatch(); //清除已经执行过的
}
statement.executeBatch(); //为了避免有剩余的,把剩下的执行掉
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
limit ?,?
,第一个参数表示的是跳过的条数,第二个参数是每页显示的条数,那么此时我们限定显示第n页,每页显示m条,那么此时的跳过的条数就是(n-1)*m
,因此此时的查询语句就是: select * from table_name limit (n-1)*m,m;
@Test
public void testLimit(){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
Scanner scanner=new Scanner(System.in); //控制台输入
try {
connection = DBUtils.getConnection(); // 获取连接
String sql = "select * from t_v limit ?,?";
statement=connection.prepareStatement(sql);
// n页,每页m条 limit (n-1)*m m
System.out.println("第几页:");
int n=Integer.parseInt(scanner.nextLine()); //页数,控制台读取的是字符串,因此这里需要转换
System.out.println("每页的条数");
int m=Integer.parseInt(scanner.nextLine()); //每页显示的条数
//设置占位符
statement.setInt(1, (n-1)*m);
statement.setInt(2, m);
//查询,获取结构集
resultSet=statement.executeQuery();
//遍历结果集
while(resultSet.next()){
int id=resultSet.getInt("id"); //获取id
String name=resultSet.getString("name"); //获取name
System.out.println(id+"-----"+name+"\t"); //制表符输出值
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
connection.setAutoCommit(false)
关闭自动提交 如果不关闭自动提交,那么会每执行一行都会提交一次connection.commit()
提交connection.rollback()
回滚create table user(id int,name,varchar(10),money int);
insert into user values(1,'超人',200),(2,'蝙蝠侠',10000);
connection.setAutoCommit(false)
@Test
public void testTransaction() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
connection.setAutoCommit(false); // 关闭自动提交
String sql = "update user set money=money+? where id=?"; // 修改蝙蝠侠和超人钱的sql语句
String sql_select = "select money from user where id=?"; // 查询钱
// 创建更新的预编译对象
statement = connection.prepareStatement(sql);
// 超人+3000
statement.setInt(1, 3000);
statement.setInt(2, 1);
int row1 = statement.executeUpdate(); // 执行更新语句
// 蝙蝠侠-3000
statement.setInt(1, -3000);
statement.setInt(2, 2);
int row2 = statement.executeUpdate(); // 执行更新语句
// 预编译查询sql语句
statement = connection.prepareStatement(sql_select);
statement.setInt(1, 2);
resultSet = statement.executeQuery(); // 执行查询语句
while (resultSet.next()) {
int money = resultSet.getInt("money"); // 获取蝙蝠侠的此时的钱
// 如果<0 抛出运行异常
if (money < 0) {
throw new RuntimeException(); // 手动抛出异常
} else { // 如果 >0 可以成功提交
connection.commit(); // 提交
}
}
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback(); // 回滚,如果运行出错,那么就回滚到起始点,数据库中就不会生效
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
@Test
public void testAuto() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
String sql="insert into t_d values(null,'神仙')";
statement=connection.createStatement();
//执行sql,并且标记此时执行需要获取生成的key值
statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
//得到生成的key值
resultSet=statement.getGeneratedKeys();
while(resultSet.next()){
int id=resultSet.getInt(1); //获取第一个值,不能写getInt("id") 因为这里不是查询得到的数据,字段名并不是id
System.out.println("自增主键的值为:"+id);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}
@Test
public void testMetaData() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection(); // 获取连接
statement=connection.createStatement();
//得到数据库元数据
DatabaseMetaData data=connection.getMetaData();
System.out.println(data.getDriverName()); //数据库驱动名字
System.out.println(data.getDriverVersion()); //驱动版本
System.out.println(data.getUserName()); //用户名
System.out.println(data.getURL()); //连接地址
System.out.println(data.getDatabaseProductName()); //数据库厂商的名称
String sql="select * from t_d";
resultSet=statement.executeQuery(sql);
//从结果集中获取表的元数据
ResultSetMetaData sqldata=resultSet.getMetaData();
int columcount=sqldata.getColumnCount(); //获取表字段的数量
//获取表中每个字段的名称
for(int i=0;i<columcount;i++){
String name=sqldata.getColumnName(i+1);
System.out.println("字段名:"+name+"\t");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(connection, statement, resultSet); // 关闭资源
}
}