JDBC-java与数据库的连接

本文为关于如何使用java语言实现连接、基础地操作数据库的实例。

目录

一.如何在java项目里与数据库的连接创建：

一.在java项目中导入相应的jar包

根据自己使用的工具的版本,选择相应的jar包，本次操作内使用的是mysql-connector-java-5.1.0-bin.jar 上述jar包下载链接

二.通过反射的方法获取jdbc驱动(driver)

若无其他操作,驱动路径一般为”com.mysql.jdbc.Driver”,写法为: Class.forName(driver);

三.获取链接

通过数据库路径、用户名、用户密码三个量，使其进行链接

DriverManager.getConnection(url, user, psw); 

url：数据库路径，一般为jdbc:mysql://localhost:本机设定端口/database_namne
user：用户自设定的数据库用户名
psw：用户自设定的数据库密码

四.相关操作
1.添加数据(增):insert;
2.删除数据(删):delete;
3.改变数据(改):update;
4.查询数据(查):select;

二.关于数据库的创建

show databases; #查询现有数据库
use database_name; #使用某个数据库
show tables; #查询数据库内现有表格
create table dept [if table not exist//中括号内容可省略,类似于java中的手动抛出异常](
        [//自定义变量名]did [//变量类型]int[//长度](4) [//属性描述]primary key auto_increment[若未结束以逗号分割], //primary key主键属性 auto_increment自增长，即id自动计数进行+1
        dName varchar(50),
        dGender varchar(2) not null/*内容不能为空*/ default'男'/*默认值为男*/  comment'性别，默认为男性'//注释 
        descM varchar(80)[//表格创建最后一行不需要添加逗号]
)[//表格类型]ENGINE=MYISAM,[//编码格式]CHARSET = utf8; 

三.相关封装代码

为了实现上述功能而进行了代码的编写与封装，为此创建了
dao:处理数据层,
entity:实体类,
utils:工具类,
impl:服务类;

dao包

1.BaseDao.java

package com.mannoroth.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


import com.mannoroth.utils.Contants;
import com.mysql.jdbc.PreparedStatement;



public class BaseDao {

    private static Connection connection = null;

    public void init(){
        try {
            Properties properties = new Properties();
            String filename = "database.properties";
            InputStream is = BaseDao.class.getClassLoader().getResourceAsStream(filename);
            properties.load(is);
            Contants.driver= properties.getProperty("driver");
            Contants.url= properties.getProperty("url");
            Contants.user= properties.getProperty("user");
            Contants.psw= properties.getProperty("password");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }


    //创建连接
    public static Connection getConnection(){
        try {
            Class.forName(Contants.driver);
            connection =  DriverManager.getConnection(Contants.url, Contants.user, Contants.psw);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }

    //关闭资源
    public static void close(Connection connection,Statement statement,ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //增删改
    public static int update (String sql,Object[] objects){
        int num = 0;
        connection = getConnection();
        java.sql.PreparedStatement ps = null;
        try {

            ps = connection.prepareStatement(sql);

            if(objects != null && objects.length >0){
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject(i+1, objects[i]);
                }

            }
            num = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally{
            close(connection, ps, null);
        }
        return num;
    }

    //全查
    public static ResultSet selectALL(String sql,Object[] objects){
        ResultSet rs = null;
        connection = getConnection();
        PreparedStatement ps = null;
        try {
            ps = (PreparedStatement) connection.prepareStatement(sql);

            if (objects!= null && objects.length>0) {
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject(i+1, objects[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally{
            close(connection, ps, null);
        }

        return rs;
    }

}

2.DeptDao.java

package com.mannoroth.dao;

import java.util.List;

import com.mannoroth.entity.Dept;

public interface DeptDao {

    int insertDept(Dept dept);

    int updateDept (Dept dept);

    int deleteDept(int did);

    List<Dept> selectDpetAll();

    List<Dept> selectDeptDid(int did);

}

impl包

DeptImpl

package com.mannoroth.dao.impl;

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

import javax.crypto.spec.PSource;

import com.mannoroth.dao.BaseDao;
import com.mannoroth.dao.DeptDao;
import com.mannoroth.entity.Dept;
import com.mysql.jdbc.PreparedStatement;


public class DeptImpl extends BaseDao implements DeptDao{

    @Override
    public int insertDept(Dept dept) {

        String sql = "insert into dept values(?,?,?,?)";

        Object[] objects = {dept.getDid(),dept.getDname(),dept.getDage(),dept.getDmess()};

        int num = this.update(sql, objects);



        return num;
    }

    @Override
    public int updateDept(Dept dept) {

        String sql = "update dept set dName = ? where did = ?";

        Object[] objects = {dept.getDname(),dept.getDid()};

        int num = this.update(sql, objects);

        return num;

    }

    @Override
    public int deleteDept(int did) {

        String sql = "delete from dept where did = ?";

        Object[] objects = {did};

        int num = this.update(sql, objects);

        return num;
    }

    @Override
    public List<Dept> selectDpetAll() {

        String sql = "select * from dept";
        ResultSet rs = this.selectALL(sql, null);
        List<Dept> list = new ArrayList<Dept>();
        try {
            while (rs.next()) {
                int did = rs.getInt("did");
                String dname = rs.getString("dname");
                int dage = rs.getInt("dage");
                String dmess = rs.getString("dmess");
                Dept dept = new Dept(did, dname, dage, dmess);
                list.add(dept);

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        for (Dept dept : list) {
            System.out.println(dept.toString());
        }
        close(null, null, rs);
        return list;
    }

    @Override
    public List<Dept> selectDeptDid(int did) {
        String sql = "select * from dept where did = ?";
        Object[] objects = {did};
        ResultSet rs = this.selectALL(sql, objects);
        List<Dept> list = new ArrayList<Dept>();
        try {
            while (rs.next()) {
                String dname = rs.getString("dname");
                int dage = rs.getInt("dage");
                String dmess = rs.getString("dmess");
                Dept dept = new Dept(did, dname, dage, dmess);
                list.add(dept);

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        for (Dept dept : list) {
            System.out.println(dept.toString());
        }
        close(null, null, rs);
        return list;    }


}

entity包

Dept.java

package com.mannoroth.entity;

public class Dept {

    private int did;
    private String dname;
    private int dage;
    private String dmess;
    public int getDid() {
        return did;
    }
    public void setDid(int did) {
        this.did = did;
    }
    public String getDname() {
        return dname;
    }
    public void setDname(String dname) {
        this.dname = dname;
    }
    public int getDage() {
        return dage;
    }
    public void setDage(int dage) {
        this.dage = dage;
    }
    public String getDmess() {
        return dmess;
    }
    public void setDmess(String dmess) {
        this.dmess = dmess;
    }
    public Dept(int did, String dname, int dage, String dmess) {

        this.did = did;
        this.dname = dname;
        this.dage = dage;
        this.dmess = dmess;
    }
    public Dept() {

    }

    public String toString() {
        return "Dept [did=" + did + ", dname=" + dname + ", dage=" + dage
                + ", dmess=" + dmess + "]";
    }


}

utils

contants.java

package com.mannoroth.utils;

public class Contants {
    public static  String driver ;
    public static  String url ;
    public static  String user ;
    public static  String psw;
}

此外，从BaseDao中可以看到有输入流的使用，为从外部读取配置文件”database.properties“。使用这样的方法能极大增强程序的扩展性。