mysql jdbc demo_jdbc里一个最靠谱的连接demo

最靠谱的jdbc连接例子

包括增删改,查一条数据,查所有数据。

Bean.java

public class Bean {

private String id;

private String number;

private String name;

private String score;

public String getNumber() {

return number;

}

public void setNumber(String number) {

this.number = number;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getScore() {

return score;

}

public void setScore(String score) {

this.score = score;

}

@Override

public String toString() {

return "Bean [id=" + id + ", number=" + number + ", name=" + name

+ ", score=" + score + "]";

}

}

JdbcUtils.java

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;

import com.mysql.jdbc.ResultSet;

import com.mysql.jdbc.Statement;

public class JdbcUtils {

private static String url="jdbc:mysql://127.0.0.1:3306/data1702?characterEncoding=UTF-8";

private static String user="root";

private static String password="123456";

//加载驱动(只加载一次)

static{

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//获得连接(静态方法独立于对象)

public static Connection getConnect() throws SQLException{

java.sql.Connection c =DriverManager.getConnection(url,user,password);

return (Connection) c;

}

//释放连接

public static void release(Statement stmt,Connection conn){

try {

stmt.close();

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void release(ResultSet rs,PreparedStatement pstmt,Connection conn){

try {

rs.close();

pstmt.close();

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

BeanDao.java

import java.sql.*;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import com.mysql.jdbc.*;

import com.mysql.jdbc.Connection;

import com.mysql.jdbc.Statement;

public class BeanDao {

// 增加一个学生

public boolean insert(String id, String number, String name, String score)

throws SQLException {

Bean b = new Bean();

int flag = 0;

Connection con = JdbcUtils.getConnect();

String sql = "insert into user (id,number,name,score) values (?, ?, ?, ?)";

PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setString(1, id);

pstmt.setString(2, number);

pstmt.setString(3, name);

pstmt.setString(4, score);

try {

flag = pstmt.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtils.release((Statement) pstmt, con);

}

if (flag != 0) {

return true;

} else

return false;

}

//删除一条记录

public boolean delete(String id) throws SQLException{

int flag = 0;

Bean b=new Bean();

Connection con = JdbcUtils.getConnect();

String sql="delete from user where id=?";

PreparedStatement pstmt=con.prepareStatement(sql);

pstmt.setString(1, id);

try {

flag=pstmt.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtils.release((Statement) pstmt, con);

}

if(flag!=0)

return true;

else

return false;

}

//修改分数

public boolean update(String id,String score) throws SQLException{

int flag = 0;

Bean b=new Bean();

Connection con = JdbcUtils.getConnect();

String sql="update user set score = ? where id = ?";

PreparedStatement pstmt=con.prepareStatement(sql);

pstmt.setString(1, score);

pstmt.setString(2, id);

try {

flag=pstmt.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtils.release((Statement) pstmt, con);

}

if(flag!=0)

return true;

else

return false;

}

//查看一条数据

public Bean select(String id) throws SQLException{

ResultSet rs=null;

Bean b=new Bean();

Connection con = JdbcUtils.getConnect();

String sql="select * from user where id = ?";

PreparedStatement pstmt=con.prepareStatement(sql);

pstmt.setString(1, id);

try {

rs=pstmt.executeQuery();

if(rs.next()){

b.setId(rs.getString("id"));

b.setNumber(rs.getString("number"));

b.setName(rs.getString("name"));

b.setScore(rs.getString("score"));

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtils.release((com.mysql.jdbc.ResultSet) rs, pstmt, con);

}

return b;

}

public List selectAll() throws SQLException{

ResultSet rs=null;

Bean b;

List list = new ArrayList();

Connection con = JdbcUtils.getConnect();

String sql="select * from user";

PreparedStatement pstmt=con.prepareStatement(sql);

try {

rs=pstmt.executeQuery();

while(rs.next()){

b=new Bean();

b.setId(rs.getString("id"));

b.setNumber(rs.getString("number"));

b.setName(rs.getString("name"));

b.setScore(rs.getString("score"));

list.add(b);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

JdbcUtils.release((com.mysql.jdbc.ResultSet) rs, pstmt, con);

}

return list;

}

}

TestJDBC.java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import java.util.Scanner;

public class TestJDBC {

public static void main(String[] args) throws SQLException {

Bean b=new Bean();

/*

测试插入

b.setId("101");

b.setNumber("1700130221");

b.setName("wangpeng");

b.setScore("100");

BeanDao bdao=new BeanDao();

boolean flag=bdao.insert(b.getId(),b.getNumber(),b.getName(),b.getScore());

System.out.println(flag);*/

/*

测试删除

b.setId("101");

BeanDao bdao=new BeanDao();

boolean flag=bdao.delete(b.getId());

System.out.println(flag);

*/

//测试修改分数

/*b.setId("100");

b.setScore("0");

BeanDao bdao=new BeanDao();

boolean flag=bdao.update(b.getId(), b.getScore());

System.out.println(flag);

*/

//查看数据库的一条数据

/*b.setId("100");

BeanDao bdao=new BeanDao();

b=bdao.select(b.getId());

System.out.println(b.getId()+" | "+b.getNumber()+" | "+b.getName()+" | "+b.getScore());*/

//查看数据库所有信息

/*BeanDao bdao=new BeanDao();

List list=bdao.selectAll();

for(Bean x:list){

System.out.println(x);

}

*/

}

}