package day03;
import java.sql.Connection;
import java.sql.Statement;
import day20151100.DBUtil2;
public class BatchDemo {
public static void main(String[] args) {
try{
Connection conn = DBUtil2.getConnection();
Statement state = conn.createStatement();
for(int i=1000;i<2000;i++){
String sql = "insert into user_ls(id) "
+ "values("+i+")";
state.addBatch(sql);
if(i%500==0){
state.executeBatch();
state.clearBatch();
}
}
state.executeBatch();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
}
}
package day03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import day20151100.DBUtil2;
public class BatchDemo2 {
public static void main(String[] args) {
try{
Connection conn = DBUtil2.getConnection();
String sql = "insert into user_ls(id,name) "
+ "values(?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i=9500;i<10500;i++){
ps.setInt(1, i);
ps.setString(2, "test"+i);
ps.addBatch();
}
ps.executeBatch();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
}
}
package day03;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import day20151100.DBUtil2;
/**
* 向dept表中追加一个部门
* 同时该部门添加一个用户
*
*/
public class JDBCDemo1 {
public static void main(String[] args) {
try{
Connection conn = DBUtil2.getConnection();
Statement state = conn.createStatement();
String sql = "insert into dept_ls values(dept_seq_ls.nextval,'java','shanghai')";
state.executeUpdate(sql);
String idSql = "select max(deptno) from dept_ls";
ResultSet rs = state.executeQuery(idSql);
int id = 0;
if(rs.next()){
id = rs.getInt(1);
}
rs.close();
String userSql = "insert into user_ls(id,name,deptno) "
+ "values(user_seq_ls.nextval,'jackson',"+id+")";
state.executeUpdate(userSql);
System.out.println("保存完毕");
state.close();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
}
}
package day03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import day20151100.DBUtil2;
/**
* preparedStatement支持一个方法
* 可以在执行插入操作后,获取该条语句
* 在数据库中产生的记录中每个字段的值
* 有个这个功能,我们在向从表中掺入数据时
* 可以获取该主键作为外键插入,而无需因为
* 要获取主表中的值而进行一次查询
*
*/
public class JDBCDemo2 {
public static void main(String[] args){
try{
Connection conn = DBUtil2.getConnection();
String deptSql="insert into dept_ls "
+ "values(dept_seq_ls.nextval,?,?)";
System.out.println(deptSql);
PreparedStatement ps = conn.prepareStatement(
deptSql,new String[]{"deptno","dname"});
ps.setString(1, "oracle");
ps.setString(2, "naijing");
if(ps.executeUpdate()>0){
ResultSet rs = ps.getGeneratedKeys();
if(rs.next()){
int deptno = rs.getInt(1);
System.out.println("该部门编号:"+deptno);
}
rs.close();
}
ps.close();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
}
}
package day03;
import java.util.List;
/**
* 业务逻辑层不再关心数据怎么来
* @author Administrator
*
*/
public class MyUserService {
public static void main(String[] args) {
UserDAO dao = new UserDAO();
if(dao.deleteById(1003)){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
}
package day03
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.util.Scanner
import day20151100.DBUtil2
public class PageDemo {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in)
System.out.println("请输入要查看的表名")
String tableName = sc.nextLine().trim()
System.out.println("请输入排序的列名:")
String colName = sc.nextLine().trim()
System.out.println("请输入一页显示的条数")
int pagesize =
Integer.parseInt(sc.nextLine().trim())
System.out.println("请输入产看的页数")
int page =
Integer.parseInt(sc.nextLine().trim())
try{
Connection conn = DBUtil2.getConnection()
String sql = "select * from ("
+ "select rownum rn,t.* from("
+ "select * from "+tableName+" order by "+colName+" "
+ ") t"
+ " )"
+ " where rn between ? and ?"
PreparedStatement ps = conn.prepareStatement(sql)
//开始
int start = (page-1)*pagesize + 1
//结束
int end = page*pagesize
ps.setInt(1, start)
ps.setInt(2, end)
ResultSet rs = ps.executeQuery()
while(rs.next()){
int rw = rs.getInt(1)
int id = rs.getInt(2)
String name = rs.getString(3)
System.out.println(rw+","+id+","+name)
}
}catch(Exception e){
}finally{
DBUtil2.closeConnection()
}
}
}
package day03;
public class User {
private int id;
private String name;
private String password;
private int money;
private String email;
private int deptno;
public User() {
super();
}
public User(int id, String name, String password, int money, String email,
int deptno) {
super();
this.id = id;
this.name = name;
this.password = password;
this.money = money;
this.email = email;
this.deptno = deptno;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
package day03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import day20151100.DBUtil2;
/**
* UserDAO
* 用于操作数据库user表的DAO
* @author Administrator
*
*/
public class UserDAO {
private static final String FIND_BY_ID_SQL = "select * from user_ls where id=?";
private static final String FIND_ALL_SQL = "select * from user_ls";
private static final String SAVE_USER = "insert into user_ls values(user_seq_ls.nextval,?,?,?,?,?)";
private static final String UPDATE_SQL ="update user_ls set name=?,password=?,"
+ "money=?,email=?,deptno=? where id=?";
private static final String DELETE_SQL="delete from user_ls where id=?";
/**
* 根据id查询对应的user记录
* @param id
* @return
*/
public User findById(int id){
try{
Connection conn = DBUtil2.getConnection();
PreparedStatement ps = conn.prepareStatement(FIND_BY_ID_SQL);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if(rs.next()){
int i = rs.getInt("id");
String name = rs.getString("name");
String pwd = rs.getString("password");
int money = rs.getInt("money");
String email = rs.getString("email");
int deptno = rs.getInt("deptno");
User user = new User(i,name,pwd,money,email,deptno);
return user;
}
ps.close();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
return null;
}
/**
* 查询所有的user记录
* @return
*/
public List<User> findAll(){
try{
Connection conn = DBUtil2.getConnection();
Statement state = conn.createStatement();
ResultSet rs = state.executeQuery(FIND_ALL_SQL);
List<User> users = new ArrayList<User>();
while(rs.next()){
int i = rs.getInt("id");
String name = rs.getString("name");
String pwd = rs.getString("password");
int money = rs.getInt("money");
String email = rs.getString("email");
int deptno = rs.getInt("deptno");
User user = new User(i, name, pwd, money, email,deptno);
users.add(user);
}
state.close();
return users;
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
return null;
}
/**
* 保存一个user信息
* @param user
* @return
*/
public boolean save(User user){
try{
Connection conn = DBUtil2.getConnection();
PreparedStatement ps = conn.prepareStatement(
SAVE_USER,new String[]{"id"});
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setInt(3, user.getMoney());
ps.setString(4, user.getEmail());
ps.setInt(5, user.getDeptno());
if(ps.executeUpdate()>0){
ResultSet rs = ps.getGeneratedKeys();
if(rs.next()){
int id = rs.getInt(1);
user.setId(id);
ps.close();
return true;
}
}
ps.close();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
return false;
}
/**
* 更新一个user信息
* @param user
* @return
*/
public boolean update(User user){
try{
Connection conn = DBUtil2.getConnection();
PreparedStatement ps = conn.prepareStatement(UPDATE_SQL);
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setInt(3, user.getMoney());
ps.setString(4, user.getEmail());
ps.setInt(5, user.getDeptno());
ps.setInt(6, user.getId());
if(ps.executeUpdate()>0){
return true;
}
ps.close();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
return false;
}
/**
* 根据id删除一个user记录
* @param id
* @return
*/
public boolean deleteById(int id){
try{
Connection conn = DBUtil2.getConnection();
PreparedStatement ps = conn.prepareStatement(DELETE_SQL);
ps.setInt(1, id);
if(ps.executeUpdate()>0){
return true;
}
ps.close();
}catch(Exception e){
e.printStackTrace();
}finally{
DBUtil2.closeConnection();
}
return false;
}
}
package day03
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Statement
import java.util.Scanner
import day20151100.DBUtil2
public class UserService {
public static void main(String[] args) {
System.out.println("请输入选项:")
System.out.println("1:注册")
System.out.println("2:登陆")
System.out.println("3:修改")
System.out.println("4:删除")
System.out.println("5:查询")
System.out.println("6:转账")
Scanner sc = new Scanner(System.in)
int option = Integer.parseInt(sc.nextLine())
switch(option){
case 1:
//注册
regUser(sc)
break
case 2:
login(sc)
break
case 3:
modify(sc)
break
case 4:
delete(sc)
break
case 5:
query(sc)
break
case 6:
giveMoney(sc)
break
}
}
public static void modify(Scanner sc){
System.out.println("请输入要修改的账户:")
String name = sc.nextLine().trim()
System.out.println("请输入新密码:")
String pwd = sc.nextLine().trim()
try{
Connection conn = DBUtil2.getConnection()
String sql = "update user_ls set password =? where name=?"
PreparedStatement ps = conn.prepareStatement(sql)
ps.setString(1, pwd)
ps.setString(2, name)
if(ps.executeUpdate()>0){
System.out.println("修改密码成功")
}else{
System.out.println("用户不存在")
}
ps.close()
}catch(Exception e){
e.printStackTrace()
}finally{
DBUtil2.closeConnection()
}
}
public static void delete(Scanner sc){
System.out.println("请输入要删除的账户:")
String user = sc.nextLine().trim()
try{
String sql = "delete from user_ls where "
+ "name=?"
System.out.println(sql)
Connection conn = DBUtil2.getConnection()
PreparedStatement ps = conn.prepareStatement(sql)
ps.setString(1, user)
if(ps.executeUpdate()>0){
System.out.println("删除成功")
}else{
System.out.println("账户不存在")
}
ps.close()
}catch(Exception e){
e.printStackTrace()
}finally{
DBUtil2.closeConnection()
}
}
public static void query(Scanner sc){
System.out.println("请输入要查询的账户:")
String user = sc.nextLine().trim()
try{
Connection conn = DBUtil2.getConnection()
String sql ="select id,name,password,money,email from user_ls "
+ "where name=?"
PreparedStatement ps = conn.prepareStatement(sql)
ps.setString(1, user)
ResultSet rs = ps.executeQuery()
if(rs.next()){
int id = rs.getInt("id")
String name = rs.getString("name")
String pwd = rs.getString("password")
int money = rs.getInt("money")
String email = rs.getString("email")
System.out.println(id+","+name+","+pwd+","+money+","+email)
}else{
System.out.println("用户不存在")
}
ps.close()
}catch(Exception e){
e.printStackTrace()
}finally{
DBUtil2.closeConnection()
}
}
public static void giveMoney(Scanner sc){
System.out.println("现在是转账操作")
System.out.println("请输入您的账号")
String fromUser = sc.nextLine().trim()
System.out.println("请输入收款人账号")
String toUser = sc.nextLine().trim()
System.out.println("请输入转出金额")
String money = sc.nextLine().trim()
//2
String countSql="select money from user_ls"
+ " where name='"+fromUser+"'"
try{
Connection conn = DBUtil2.getConnection()
//关闭自动提交事务
//开始纳入事务控制,因为开始执行DML操作了
conn.setAutoCommit(false)
Statement state = conn.createStatement()
ResultSet rs = state.executeQuery(countSql)
//判断是否查出数据
if(rs.next()){
int count = rs.getInt("money")
//判断余额是否够
if(count>=Integer.parseInt(money)){
String fromSql = "update user_ls set money=money-"+money+" "
+ "where name='"+fromUser+"'"
//修改汇款人余额
if(state.executeUpdate(fromSql)>0){
//修改收款人余额
String toSql = "update user_ls set money=money+"+money+" "
+ "where name='"+toUser+"'"
if(state.executeUpdate(toSql)>0){
System.out.println("转账成功")
conn.commit()
}else{
System.out.println("转账失败")
conn.rollback()
}
}
}else{
System.out.println("余额不足")
}
}else{
System.out.println("没有该用户"+fromUser)
}
}catch(Exception e){
e.printStackTrace()
}finally{
DBUtil2.closeConnection()
}
}
public static void regUser(Scanner sc){
try{
//1
System.out.println("现在是注册操作:")
System.out.println("请输入用户名:")
String user = sc.nextLine().trim()
System.out.println("请输入密码:")
String pwd = sc.nextLine().trim()
System.out.println("momey:")
String money = sc.nextLine().trim()
System.out.println("mail:")
String email = sc.nextLine().trim()
//2
Connection conn = DBUtil2.getConnection()
//3
Statement state = conn.createStatement()
//4
String sql = "select max(id) id from user_ls"
//5
ResultSet rs = state.executeQuery(sql)
int id = -1
if(rs.next()){
id = rs.getInt("id")
}
System.out.println("当前最大id是:"+id)
//统计处最大值后对id加1
id++
rs.close()
//6
sql = "insert into user_ls values" +
"("+id+",'"+user+"','"+pwd+"',"+money+",'"+email+"')"
System.out.println(sql)
if(state.executeUpdate(sql)>0){
System.out.println("注册成功,欢迎你"+user)
}else{
System.out.println("呵呵")
}
}catch(Exception e){
e.printStackTrace()
}finally{
DBUtil2.closeConnection()
}
}
public static void login(Scanner sc){
System.out.println("现在是登陆操作:")
System.out.println("输入用户名:")
String user = sc.nextLine().trim()
System.out.println("pwd:")
String pwd = sc.nextLine().trim()
try{
Connection conn = DBUtil2.getConnection()
//Statement state = conn.createStatement()
//String sql = "select * from user_ls " +
// "where LOWER(name)=LOWER('"+user+"') and password = '"+pwd+"'"
String sql = "select * from user_ls where " +
"name=? and password=?"
PreparedStatement ps = conn.prepareStatement(sql)
ps.setString(1, user)
ps.setString(2, pwd)
ResultSet rs = ps.executeQuery()
//ResultSet rs = state.executeQuery(sql)
//根据用户输入能否查到数据
if(rs.next()){
System.out.println("登录成功")
}else{
System.out.println("用户名或密码错误")
}
// if(state.execute(sql)){
// System.out.println("")
// }
}catch(Exception e){
e.printStackTrace()
}finally{
DBUtil2.closeConnection()
}
}
}