java小案例(音乐管理 增删改查)

java小案例(音乐管理)

前期准备

1.加入需要的jar包 2.建立需要的文件格式

将jdbc的几个步骤封装起来

package com.zhongruan.util;

import java.sql.*;

public class Dbutil {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/zjgm?user=root&password=xiayin0001&characterEncoding=utf-8&useSSL=true");
        return connection;
    }
    public static void Closeall(ResultSet resultSet, Statement statement, Connection connection){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

改变

package com.zhongruan.util;

import com.zhongruan.music.Music;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement statement=null;
        List<Music>  musics=new ArrayList<>();
        try {
            connection=Dbutil.getConnection();
            String sql1 = "UPDATE music SET music.name = 'My Last Love' where id=1";
            String sql="select * from music";
            statement=connection.prepareStatement(sql1);
            statement=connection.prepareStatement(sql);
            statement.executeUpdate(sql1);
            resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                Music music=new Music();
                music.setId(resultSet.getInt(1));
                music.setName(resultSet.getString(2));
                music.setAuthor(resultSet.getString(3));
                musics.add(music);
                System.out.println(music);
            }
            System.out.println(musics);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.Closeall(resultSet,statement,connection);
        }
    }
}

增加

package com.zhongruan.util;

import com.zhongruan.music.Music;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement statement=null;
        List<Music>  musics=new ArrayList<>();
        try {
            connection=Dbutil.getConnection();
            String sql1="INSERT INTO music (name,author) VALUES('Nacy','he love')";
            String sql="select * from music";
            statement=connection.prepareStatement(sql1);
            statement=connection.prepareStatement(sql);
            statement.executeUpdate(sql1);
            resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                Music music=new Music();
                music.setId(resultSet.getInt(1));
                music.setName(resultSet.getString(2));
                music.setAuthor(resultSet.getString(3));
                musics.add(music);
                System.out.println(music);
            }
            System.out.println(musics);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.Closeall(resultSet,statement,connection);
        }
    }
}

删除

package com.zhongruan.util;

import com.zhongruan.music.Music;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement statement=null;
        List<Music>  musics=new ArrayList<>();
        try {
            connection=Dbutil.getConnection();
            String sql1 = "DELETE FROM music WHERE student.name = 'Nacy'";
            String sql="select * from music";
            statement=connection.prepareStatement(sql1);
            statement=connection.prepareStatement(sql);
            statement.executeUpdate(sql1);
            resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                Music music=new Music();
                music.setId(resultSet.getInt(1));
                music.setName(resultSet.getString(2));
                music.setAuthor(resultSet.getString(3));
                musics.add(music);
                System.out.println(music);
            }
            System.out.println(musics);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.Closeall(resultSet,statement,connection);
        }
    }
}

查询

package com.zhongruan.util;

import com.zhongruan.music.Music;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args){
        ResultSet resultSet=null;
        Connection connection=null;
        PreparedStatement statement=null;
        List<Music>  musics=new ArrayList<>();
        try {
            connection=Dbutil.getConnection();
            String sql="select * from music";
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                Music music=new Music();
                music.setId(resultSet.getInt(1));
                music.setName(resultSet.getString(2));
                music.setAuthor(resultSet.getString(3));
                musics.add(music);
                System.out.println(music);
            }
            System.out.println(musics);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.Closeall(resultSet,statement,connection);
        }
    }
}