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();
							}
						}
					}
				}
				
	}

}

createSql.java

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();

	}

}