7.JdbcTools,JDBC的基本使用

/***************JdbcTools,JDBC*********************************/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JdbcTools {

static String url = "jdbc:mysql://10.225.78.24:3306/pipi";//10.225.78.24 :sql服务器IP
static String name = "root";
static String password = "root";

/**
* 注册驱动jdbc
*/
static{
try {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


/**
* 数据库连接
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, name, password);
}


/**
* 关闭数据库
*/
public static void close(Connection conn,Statement st,ResultSet rs){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try{
if(st != null){
st.close();
}
}catch(SQLException e){
e.printStackTrace();
}finally{
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}
}


/**
* 插入数据
*/
public static void insertData() throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.建立连接
conn = getConnection();
//3.执行sql语句
String sql = "insert into t_pipi values(?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setObject(1, "1");
pstmt.setObject(2, "pipi");
pstmt.setObject(3, "92");
pstmt.setObject(4, "62");
pstmt.setObject(5, "82");
pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{
//4.关闭资源
close(conn,pstmt,null);
}
}



/**
* 删除数据
*/
public static void deleteData() throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
try{

conn = getConnection();

String sql = "delete from t_pipi where name='pipi1' ";
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{

close(conn,pstmt,null);
}
}


/**
* 更新数据
*/
public static void updateData() throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
try{

conn = getConnection();

String sql = "update t_pipi set name='pipi1' where id = 1  ";
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{

close(conn,pstmt,null);
}
}


/**
* 查询数据
*/
public static void selectData() throws Exception{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{

conn = (Connection) DriverManager.getConnection(url, name, password);

   st = conn.createStatement();
rs = st.executeQuery("select * from t_pipi");
while(rs.next()){
System.out.println("id="+rs.getObject("id")+"---> name="+rs.getString("name"));
}
}catch (Exception e){
e.printStackTrace();
}finally{

rs.close();
st.close();
conn.close();
}
}


        /**
* 批量插入数据
*/
public static void insertBatch() throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
try{

conn = JdbcTools.getConnection();

pstmt = conn.prepareStatement("insert into t_pipi values(?,?,?,?,?,?)");
for(int i = 0;i<10;i++){
pstmt.setObject(1, "33"+i);
pstmt.setObject(2, "xiao33");
pstmt.setObject(3, 23);
pstmt.setObject(4, 23);
pstmt.setObject(5, 23);
//pstmt.setDate(6,new Date(22222222));
//pstmt.setString(6, "1992-01-12");


pstmt.addBatch();
}

pstmt.executeBatch();

}catch (Exception e){
e.printStackTrace();

}finally{

JdbcTools.close(conn,pstmt,null);
}
}


}


/******************jdbc事务*******************************/


public static void insertShiwu() throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
//Savepoint sp = null;
try{

conn = JdbcTools.getConnection();


//conn.setAutoCommit(false);//设置事务
//conn.prepareCall();//调度存储过程


pstmt = conn.prepareStatement("insert into t_pipi () values()");
pstmt.executeUpdate();
//sp= conn.setSavepoint();//设置保存点

pstmt = conn.prepareStatement("insert into t_pipi (id,name,chinese,english,math) values(null,'ddd',55,66,88)");
pstmt.executeUpdate();

pstmt = conn.prepareStatement("insert into t_pipi (id,name,chinese,english,math) values(null,'ddd',65,78,94)");
pstmt.executeUpdate();

}catch (Exception e){
e.printStackTrace();
//conn.rollback();//回滚
//conn.rollback(sp);//回滚到保存点
}finally{
//conn.commit();//提交事务

JdbcTools.close(conn,pstmt,null);
}
}





/****************JDBC操作sql步骤********************************/




//1.
Class clazz = Class.forName("com.mysql.jdbc.Driver"); 

//2.
//Connection conn = DriverManager.getConnection("jdbc:mysql:///sqlName", "root", "root");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sqlName", "root", "root");

//3.sql(Statement)
Statement st = conn.createStatement();
st.executeUpdate("insert into t_student values(null,'zs',20)");

//4.
ResultSet rs = st.executeQuery("select * from t_student where name='zs'");
while(rs.next()){
//rs.getObject("id");
//rs.getString("name");
System.out.println("id="+rs.getObject("id")+"---> name="+rs.getString("name"));
}

//5.
rs.close();
st.close();
conn.close();










/*******************jdbc case ?student????******************************/
1.????????
2.??JdbcTools
3.???:
package gzt.jdbc?JdbcTools;
package gzt.jdbc.stu.domain;
package gzt.jdbc.stu.DAO;


4.??domain?
package gzt.jdbc.stu.domain;


public class Student {
private int id;
private String name;
private int age;
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}


}


5.??DAO??
public interface IStudentDao {
public int save(Student st);



public boolean delete(Student st);
public boolean delete(int id);
public void update(Student st);
public Student get(int id);
public Student get(Student st);
public List<Student> getAll();



}


5.??DAO?


public class TestDao implements IStudentDao {


@Override
public int save(Student st) {
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
pstmt = conn.prepareStatement("insert into student values(?,?,?)");
pstmt.setObject(1,11);
pstmt.setObject(2,"pipi");
pstmt.setObject(3,33);
return pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}
return 0;
}


@Override
public boolean delete(Student st) {
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
pstmt = conn.prepareStatement("delete from student where sname=? ");
pstmt.setString(1, st.getName());
pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}

return false;
}




@Override
public boolean delete(int id) {

Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
pstmt = conn.prepareStatement("delete from student where id= "+id);
pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}

return false;
}


@Override
public void update(Student st) {
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
String sql = "update student set sname='p2' where id =? ";
pstmt = conn.prepareStatement(sql);
pstmt.setObject(1, st.getId());
pstmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}


}


@Override
public Student get(int id) {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
pstmt = conn.prepareStatement("select * from student where id= "+id);
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println("id="+rs.getObject("id")+",name="+rs.getString("sname")+",age="+rs.getString(3));
}
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}

return null;
}


@Override
public Student get(Student st) {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
pstmt = conn.prepareStatement("select * from student where sname= ? ");
pstmt.setString(1, st.getName());
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println("id="+rs.getObject("id")+",sname="+rs.getString("sname")+",age="+rs.getString(3));
}
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}
return null;
}


@Override
public List<Student> getAll() {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try{
//2.????
conn = JdbcTools.getConnection();
//3.????sql???
pstmt = conn.prepareStatement("select * from student  ");
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println("id="+rs.getObject("id")+",name="+rs.getString("sname")+",age="+rs.getString(3));
}
}catch (Exception e){
e.printStackTrace();
}finally{
//4.????
JdbcTools.close(conn,pstmt,null);
}

return null;
}




}


6.testing calss
public class Demo1 {


public static void main(String[] args) {
// TODO Auto-generated method stub
TestDao ta = new TestDao();
Student st = new Student();
//ta.save(new Student());

//st.setName("p2");
//ta.delete(st);

//ta.delete(2);

//public List<Student> getAll()
/*List<Student> list = new ArrayList<Student>();
list.add(new Student());*/
//ta.getAll();

//st.setId(3);
//ta.update(st) ;

//ta.get(3); 

//st.setName("p4");
//ta.get(st);
}


}




/*************************************************/