数据库操作之增删改查

数据库连接与关闭函数（DBUtil.java）

package com.po;

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

public class DBUtil {
	private static String Driver;
	private static String url;
	private static String username;
	private static String password;
	static 
	{
		Driver ="com.mysql.jdbc.Driver";
		url="jdbc:mysql://localhost:3306/Geek99DB";
		username="root";
		password="123456";
	}
	public static Connection open()
	{
		try {
			Class.forName(Driver);
			return DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	public static void close(Connection conn)
	{
		if(conn!=null)
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	

}

创建一个mycar类进行数据库查询的时候保存到一个数组

package com.po;

public class mycar {
	private String carName;
	private String carId;
	private int carNum;
	public String getCarName() {
		return carName;
	}
	public void setCarName(String carName) {
		this.carName = carName;
	}
	public String getCarId() {
		return carId;
	}
	public void setCarId(String carId) {
		this.carId = carId;
	}
	public int getCarNum() {
		return carNum;
	}
	public void setCarNum(int carNum) {
		this.carNum = carNum;
	}
	public String toString()
	{
		return carName+","+carId+","+carNum;
	}

}

数据库增删改查数据

package com.po;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;



public class test {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//createTable();
		//insert();
		//update();
		//delete();
		//query();
		//queryArr();
	}
	public static List<mycar> queryArr()
	{
		Connection conn=DBUtil.open();
		String sql="select * from mycar";
		try {
			Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			List<mycar> list=new ArrayList<mycar>();
			while(rs.next())
			{
				String carName=rs.getString(1);
				String carId= rs.getString(2);
				int carNum =rs.getInt(3);
				//System.out.println(carName+","+carId+","+carNum);
				mycar car =new mycar();
				car.setCarName(carName);
				car.setCarId(carId);
				car.setCarNum(carNum);
				list.add(car);
			}
			System.out.println(list);
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.close(conn);
		}
		return null;
	}
	public static void query()
	{
		Connection conn=DBUtil.open();
		String sql="select * from mycar";
		try {
			Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next())
			{
				String carName=rs.getString(1);
				String carId= rs.getString(2);
				int carNum =rs.getInt(3);
				System.out.println(carName+","+carId+","+carNum);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.close(conn);
		}
	}
	public static void delete()
	{
		Connection conn=DBUtil.open();
		String sql="delete from mycar where carnum=5";
		try {
			Statement stmt=conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.close(conn);
		}
	}
	public static void update()
	{
		Connection conn=DBUtil.open();
		String sql="update mycar set carname='auto' where carid='88888888'";
		try {
			Statement stmt=conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.close(conn);
		}
	}
	public static void insert()
	{
		Connection conn=DBUtil.open();
		String sql="insert into mycar(carName,carId,carNum) values('BMW','88888888',5);";
		try {
			Statement stmt=conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.close(conn);
		}
	}
	public static void createTable()
	{
		Connection conn=DBUtil.open();
		String sql="create table mycar(carName varchar(20),carId varchar(20),carNum int);";
		try {
			Statement stmt=conn.createStatement();
			stmt.execute(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.close(conn);
		}
	}

}