mysql數據庫的連接以及增刪改查Java代碼實現（Statement版）

mysql數據庫的連接以及增刪改查Java代碼實現（Statement版）

數據庫：

create table t1(id int primary key not null auto_increment,name varchar(32),password varchar(32));

insert into t1(name,password) values('admin','123');
insert into t1(name,password) values('zhangsan','123');
insert into t1(name,password) values('lisi','123');

Java代碼：

mysqlDao.java：

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class mysqlDao {
	
	private static String driver="com.mysql.jdbc.Driver";     //驅動
	private String url="jdbc:mysql://localhost:3306/test";    //數據庫連接地址
	private String user="root";     //數據庫賬戶
	private String password="11";      //數據庫密碼
	
	private Connection connection=null;      //連接
	private Statement stmt=null;      //聲明
	private ResultSet rs=null;     //結果集
	
	private int i=-1;
	
	/*
	 * 創建驅動
	 * */
	static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/*
	 * 加載驅動
	 * */
	@Test
	public void connect() {
		// TODO Auto-generated method stub
		try {
			
			connection=DriverManager.getConnection(url, user, password);
			
			if(connection!=null){
				System.out.println("數據庫連接成功！");
			}else{
				System.out.println("數據庫連接失敗！");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/*
	 * 執行sql語句
	 * */
	public void doSql(String sql) {
		// TODO Auto-generated method stub
		
		System.out.println("This Is mysqlDao.doSql() Method!");
		
		if(sql!=null){
			connect();
			
			try {
				
				stmt=connection.createStatement();
				stmt.execute(sql);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}
	
	/*
	 * 獲取影響行數
	 * */
	public int getUpCount() {
		// TODO Auto-generated method stub
		
		try {
			i=stmt.getUpdateCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}
	
	/*
	 * 獲取結果集
	 * */
	public ResultSet getRs() {
		
		try {
			rs=stmt.getResultSet();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}
	
	/*
	 * 執行關閉方法
	 * */
	public void close() {
		// TODO Auto-generated method stub
		
			try {
				if(rs!=null){
				rs.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				
					try {
						
						if(stmt!=null){
						stmt.close();
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally{
						if(connection!=null){
							try {
								connection.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}
					}
				}
				
	}

}

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

public class createSql {
	
	mysqlDao mysqldao=new mysqlDao();
	
	private String sql;
	private int i=-1;
	
	private ResultSet rs=null;
	
	/*
	 * 插入數據
	 * */
	@Test
	public void insert() {
		// TODO Auto-generated method stub
		
		sql="insert into t1(name,password) values('lisi','1234')";    //創建sql語句
		mysqldao.doSql(sql);    //執行sql語句
		i=mysqldao.getUpCount();     //獲取影響行數
		if(i!=-1){
			System.out.println("數據插入成功！");
		}else{
			System.out.println("數據插入失敗！");
		}
		mysqldao.close();      //關閉連接
		
	}
	
	/*
	 * 刪除數據
	 * */
	@Test
	public void delete() {
		// TODO Auto-generated method stub
		sql="delete from t1 where id=6";
		mysqldao.doSql(sql);
		i=mysqldao.getUpCount();
		if(i!=-1){
			System.out.println("數據刪除成功！");
		}else{
			System.out.println("數據刪除失敗！");
		}
		mysqldao.close();
	}
	
	/*
	 * 修改數據
	 * */
	@Test
	public void update() {
		// TODO Auto-generated method stub
		sql="update t1 set name='wangwu' where id=1";
		mysqldao.doSql(sql);
		i=mysqldao.getUpCount();
		if(i!=-1){
			System.out.println("數據更新成功！");
		}else{
			System.out.println("數據更新失敗！");
		}
		mysqldao.close();
	}
	
	/*
	 * 遍歷數據
	 * */
	@Test
	public void select() throws SQLException {
		// TODO Auto-generated method stub
		
		sql="select * from t1";
		mysqldao.doSql(sql);
		
		rs=mysqldao.getRs();
		
		if(rs!=null){
			rs.last();
			i=rs.getRow();
			
			if(i>0){
				rs.beforeFirst();
				while(rs.next()){
					
					String id=rs.getString("id");
					String name=rs.getString("name");
					String password=rs.getString("password");
					System.out.println("id:"+id+"  "+"name:"+name+"  password:"+password);
				}
			}else{
				System.out.println("對不起，沒有您要查詢的信息！");
			}
			
		}else{
			System.out.println("異常...........");
		}
		mysqldao.close();

	}

}