java连接数据库以及基本操作

1、打开ＳＱＬｙｏｇ　首先创建一个数据库，然后再数据库下面创建一个表，填写好表名，

然后列名出写好相应的表头内容，选择好后面相应的属性，例如ｉｄ，ｎａｍｅ，ｔｅｌ，

２、导入jar包

  新建一个java工程，选中工程，右击鼠标，new一个folder，把jar包复制进去，然后选中的jar包，右击鼠标，build path 选中add....

３、java连接数据库

package cn.edu.hpu.db;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Connection {
public static void main(String[] args) {
 
      java.sql.Connection conn = null;    //Connection是java本身具有的类
try {
Class.forName("com.mysql.jdbc.Driver");  //连接必备语句
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block  //用try catch 块进行捕捉错误
e.printStackTrace();
}
try {
conn =  DriverManager.getConnection("jdbc:mysql:   //localhost:3306/students","root","root");

//连接必备语句，students为所建立的数据库的名字，root 为数据库用户名，root 为密码
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null)
{
System.out.println("连接成功");
}
else
{
System.out.println("连接不成功");
} 
}
}

4、添加

package cn.edu.hpu.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class Insert {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/students","root","root");//连接
String sql = "insert into student(name,tel) value(?,?)";    //sql语句，？？对应下面1,2
PreparedStatement pst = conn.prepareStatement(sql);     //准备状态
pst.setString(1, "ycc");
pst.setString(2, "000");      //1,2分别为对应的第一个问号，和另外一个问号
System.out.println("完成添加！");
int rows = pst.executeUpdate();    //受影响的行数
if (rows > 0) {
System.out.println("完成"); 
}
else{
System.out.println("插入失败！");
}//这部分必须写，不然添加不成功

}
}

5、删除

package cn.edu.hpu.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Delete {
public static void main(String[] args) {
boolean fault = false;
Connection conn = null;
PreparedStatement pst = null;
int id = 4;//删除对应的id
try {
conn = DBOperator.getConnection();
String sql = "delete from student where id = ?";    //删除的sql语句
pst = conn.prepareStatement(sql);
pst.setInt(1, id);   
int rows = pst.executeUpdate();
if (rows > 0) {
fault = true;
}
} catch (Exception e) {
e.printStackTrace();
}
finally{
DBOperator.close(pst,conn);
}
if(fault)
{
System.out.println("删除成功");
}
else
{
System.out.println("删除失败");
}
}
}

6、查找

package cn.edu.hpu.db;
import java.beans.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Search {
public static void main(String[] args) {
Student stu =GetList(7) ;
System.out.println(stu.getId());
System.out.println(stu.getName());
System.out.println(stu.getTell());

}
public static Student GetList(int id)
{
Student student = new Student();
java.sql.Connection conn = null;
java.sql.Statement st = null;
ResultSet r = null;

try {
conn = DBOperator.getConnection();
String sql = "select * from student where id = " + id;  //id为整型，必须为整型，sql语句不要写错了，
st =  conn.createStatement();

r =  st.executeQuery(sql);
if(r.next())
{
student.setId(r.getInt("id"));
student.setName(r.getString("name"));  //调用set和get 语句来提取出信息
student.setTell(r.getString("tel"));

}
} catch (Exception e) {
e.printStackTrace();
}
return student;
}
}

7、修改

package cn.edu.hpu.db;
import java.sql.SQLException;
import com.mysql.jdbc.PreparedStatement;
public class Updata {  //  进行更新
public static void main(String[] args) {
Student stu = new Student();
Search sh = new Search();
stu = sh.GetList(5);  
stu.setName("ddc");
stu.setTell("123");
boolean fault = false;
java.sql.Connection conn = null;
java.sql.PreparedStatement pst = null;
try {
conn = DBOperator.getConnection();   //调用总方法里的连接方法
String sql = "update student set name = ?,tel = ? where id = ?";   //where前面没有逗号
pst = conn.prepareStatement(sql);
pst.setString(1, stu.getName());
pst.setString(2, stu.getTell());
pst.setInt(3, stu.getId());
int rows = pst.executeUpdate();
if(rows>0)
{
System.out.println("更新成功");
}
else{
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

8、DBOpreator

package cn.edu.hpu.db;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.ConcurrentHashMap;
public class DBOperator {
private final static String qudong = "com.mysql.jdbc.Driver";
private final static String adress = "jdbc:mysql://localhost:3306/students";
static{
try {
Class.forName(qudong);
} 
catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection()
{
Connection conn = null;
try {
conn = DriverManager.getConnection(adress, "root", "root");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet r,PreparedStatement pst,Connection conn)
{
try {
if (r != null) {
r.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(PreparedStatement pst,Connection conn)
{
close(null,pst,conn);
}
}