mysql数据库的连接和操作

1.前期准备：
数据库准备：


Java建包：

bean包一般是写数据库内的实体类
dao包一般是写对数据库的操作
main包一般是编写主方法
util包主要是工具类和数据库连接
附注：还要记得导入mysql.jar文件
2.bean包：
Animals类：

package com.jikexueyuan.bean;

public class Animals {

    private int id;
    private String name;
    private int age;
    private int anId;
    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;
    }
    public int getAnId() {
        return anId;
    }
    public void setAnId(int anId) {
        this.anId = anId;
    }




}

AnType类：

package com.jikexueyuan.bean;

public class AnType {

    private int anId;
    private String anName;
    public int getAnId() {
        return anId;
    }
    public void setAnId(int anId) {
        this.anId = anId;
    }
    public String getAnName() {
        return anName;
    }
    public void setAnName(String anName) {
        this.anName = anName;
    }



}

2.dao包：
AnimalsDAO

package com.jikexueyuan.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.PseudoColumnUsage;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.jikexueyuan.bean.Animals;
import com.jikexueyuan.util.BaseConnection;


public class AnimalsDAO {

    public ArrayList<Animals> getList(){
        ArrayList<Animals> ar = new ArrayList<Animals>();
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null; 
        String sql = "select * from Animals";
        try {
            //prepareStatement是SQL执行器对象，获取数据库数据
            ps = conn.prepareStatement(sql);
            //执行查询操作，把所有的查询放在rs结果集中
            rs = ps.executeQuery();
            while (rs.next()) {
                Animals an = new Animals();
                an.setId(rs.getInt("id"));
                an.setName(rs.getString("name"));
                an.setAge(rs.getInt("age"));
                an.setAnId(rs.getInt("anid"));
                ar.add(an);
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        //关闭资源
        finally{
            BaseConnection.closeRes(conn, ps, rs);
        }
        return ar;
    }

}

AnTypeDAO包：

package com.jikexueyuan.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.jikexueyuan.bean.AnType;
import com.jikexueyuan.bean.Animals;
import com.jikexueyuan.util.BaseConnection;

public class AnTypeDAO {

    public ArrayList<AnType> getList(){
        ArrayList<AnType> ar = new ArrayList<AnType>();
        Connection conn = BaseConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null; 
        String sql = "select * from antype";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                AnType an = new AnType();
                an.setAnId(rs.getInt("anid"));
                an.setAnName(rs.getString("anname"));
                ar.add(an);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseConnection.closeRes(conn, ps, rs);
        }
        return ar;
    }

}

3.main包：

package com.jikexueyuan.main;

import java.util.ArrayList;

import com.jikexueyuan.bean.AnType;
import com.jikexueyuan.bean.Animals;
import com.jikexueyuan.dao.AnTypeDAO;
import com.jikexueyuan.dao.AnimalsDAO;

public class TestMain {

    public static void main(String[] args) {
        AnimalsDAO ad = new AnimalsDAO();
        ArrayList<Animals> ar = ad.getList();
        for (Animals an:ar) {
            System.out.println(an.getName());
        }
        AnTypeDAO  atd = new AnTypeDAO();
        ArrayList<AnType> arr = atd.getList();
        for(AnType an:arr){
            System.out.println(an.getAnName());
        }
    }

}

4.util包：
BaseConnection

package com.jikexueyuan.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class BaseConnection {

    //编写数据库静态连接方法
    public static Connection getConnection(){
        Connection conn = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/animal","root","123321");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭资源的方法
    public static void closeRes(Connection conn,PreparedStatement ps,ResultSet rs){
        try {
            if (ps!=null) {
                ps.close();
            }
            if (rs!=null) {
                conn.close();
            }
            if (conn!=null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void closeRes(Connection conn,PreparedStatement ps){
        try {
            if (ps!=null) {
                ps.close();
            }
            if (conn!=null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}