本文为关于如何使用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“。使用这样的方法能极大增强程序的扩展性。